Forum Discussion

Matt Lunt's avatar
Matt Lunt
Copper Contributor
Aug 19, 2018

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 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!

excel
  • Arul Tresoldi's avatar
    Arul Tresoldi
    Iron Contributor
    Aug 19, 2018

    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 ;)

Resources