Need to delete Duplicate active cell in ROW (ROOM or Subject)

Hello,
Thanks lot your Interest about my Problem and also appreciate your support.

I have excel sheet I need to prevent duplicate entry on SUB or ROOM in one ROW.

As example after I enter RM# as 1 in E4, This need to stop pick the room 1 in entire Row, also I link through data Validation .

if Some one pick the RM# 1 in Cell Q1, That Cell need to delete by popping up Error of " Entering Duplicate value not allowed"


also need to do same for Subject ("SUB" )as well.

Best regard

Indika

Hello,
Thanks lot for your respond, Yes I found the same solution earlier as well.
I need to keep my data validation for select the subject and Room, But if I use this formula for data validation I can not do that.

So I need to keep my data validation as its and Duplicate control need to do using VBA.

Thanks Again

Best regard
Indika

Dear Bernie,
Thank lot for the all support because it is working well.
if Possible Please help to get this same result only for E, G, I , K, M, O, Q and S (Which mention only Room # - RM# in Row 3)

Best regard
Indika

Hello Bernie,
I just check it Properly, So it crashing with date and week number, So please help to support to apply for range D:S as one option.

Also as I mention above as 2nd Option need to apply the only for RM# Column.

So sorry for the confusion

Best regard
Indika

Dear Bernie,
Thanks lot for all support, it working well.

Best regard
Indika

Dear Bernie,
So sorry to come back to you again,
Because in My Excel i am having heading till 3rd Row,
So I Try to use a different heading to Overcome to from this But it not help to get final format.

So please Help me to take out the 1st 3 Rows from Equation

Then Final formula need to Skip first 3 Column, and Then need to apply "E:E, G:G, I:I , K:K, M:M, O:O, Q:Q, S:S"

In the mean time if you can help me to select only Specify Rows Range like 4:1000, That also Help.

Best regard
Indika