conditional format a column based on conditional formatting of a different column

[E. Jones]

Hello once again all, here is my current problem.

I need to be able to conditionally format information in column B based on the conditional formatting of column C.

Example: Column B contains several hundred entries and some have the same information but entered differently ("The dog is black", and "Dog, black"), but the information in Column C is the same identifier (45623, 45623) which is conditionally formatted to highlight duplicates. However, "The dog is black" may be in row 20, and "Black Dog" may be in row 125. What I need is when duplicates are conditionally formatted in C, that the same conditional format shows in Column B which will enable me to fix what I need to fix in Column. (I don't think I explained that well). Clearly I can't conditionally format Column B for duplicates as there will not be any.

Also, is it possible to highlight the first entry "The dog is black" in one color, and highlight the second entry "Dog, black" in a different color, as doing so will help me to know which of the two I end up deleting later.

I know I did not explain this well, and did not provide a sheet example, but if anyone understands what I am asking and can help, it will be very much appreciated.

 

[etaf]

if the number is in C you could use that

B20 = "The dog is black" & C20 = 45623
B125 = "black dog" & C125 = 45623

then select the range B1:C10000
conditional formatting
=countif( $C$1:$C$10000, C1)>1
that should highlight both B and C

you could then use different colours if the count is 2, 3, 4

 

[E. Jones]

Wayne,

Awesome! This is exactly what I needed, thanks so much! One question though; "you could then use different colours if the count is 2, 3, 4", , How do I set up the conditional formatting for the different colors for 2, 3, 4 etc.?​

 

[etaf]

so if you had say
ID 45623 is in the list twice it could be red
IF
ID 45623 is in the list three times it could be blue
BUT it will highlight each one blue only

is that anygood

if so a rule
=countif( $C$1:$C$10000, C1)=2
fill colour RED

new rule
=countif( $C$1:$C$10000, C1)=3
fill colour BLUE

new rule
=countif( $C$1:$C$10000, C1)>3
fill green
so any ID in the list 4 or more times ALL would be coloured GREEN

 

[E. Jones]

Yes, thanks again, this is exactly what I needed!

 

[etaf]

you are welcome