Forum Discussion
Conditional Formatting - Change colour if name in cell 'x' also appears in list 'y-z'
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 g...
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 ;)