Conditional Formatting - Change colour if name in cell 'x' also appears in list 'y-z'

[Matt Lunt]

Hi everyone,

My company has a scheduling spreadsheet and if a person has a particular qualification (fed by being in column A on a separate sheet) they are formatted red. Amber for column B and green for column C.

How do I make a rule that states something like "if (sheet 1) 'B5' appears in (sheet 2) B2-9, then fill red"?

Thank you!

[#2]

Have you tried with a COUNTIF in a conditional formatting rule by formula?

COUNTIF(sheet2!B2:B9;sheet1!B5)

If that is >0 then means that B5 is at least 1 time in the interval B2:B9; then fill with red background or whatever you need.

Alternative version is I think as easier as non-elegant: add a column that you'll hide before saving and closing the document.

In this new column, use:

IF(COUNTIF(sheet2!B2:B9;sheet1!B5)>1;SAME CELL AS THE ONE YOU WANT TO PAINT RED;"")

Then with a conditional formatting, set that cell painted red if it's equal to the one in which you just inserted that formula I wrote.

Then hide that column to avoid double cells ;)