Solved: The Art of Conditional Formatting!

[shrivallabha]

In the last week, I realised how (badly) we have underused Named Ranges and Conditional Formatting up till now.

I am attaching a workbook where I have used conditional formatting. In it, I have used one formula specifically with varying named ranges.

However, I tripped on trying to set conditional formatting on the first column.
1. In this column we use a ID which continues for non-fixed number of rows. Then the user shall enter another ID.
2. After this however, the previous ID should not be used i.e. ABCD in the attached sheets' case.
3. In that case, the first row where the ID is retyped should go red which will tell user that the ID has been used previously (at row 16).
4. This will help user not to repeat the ID entry by mistake and take appropriate action.

I first tried Excel's built in help. But it turned out to be no help . As it simply tells us that the formula should yield TRUE or FALSE and there are no examples.

Of course, I can go the VBA way to check things but this art of CF is somehow fascinating me. Thank you in advance.

[Bob Phillips]

shrivallabha,

You would use a formula like

=COUNTIF(B$1:B2,B2) > 1

in cell B2 copied down

What this does is that in B2 the formula being applied would be

=COUNTIF(B$1:B2,B2) > 1

and that would be False, as we are effectively looking at just the first cell.

In B3 the formula being applied would be

=COUNTIF(B$1:B3,B3) > 1

and this would be TRUE with your data because ABCD is repeated.

And so on down the column.

[shrivallabha]

First of all apologies, Column A was example column and Column B was how the results should come. I had not written this. If the user used ID continuously for as many rows he needed, no problem. However, what the user should not do is sandwiching another ID in between rows (bcd between abcd). Consecutive Rows were OK.

Bob, your formula gave me the clue. Following formula does it:

=AND(A2<>A1,COUNTIF(A$1:A2,A2) > 1)

Thank you for giving helping hand.