Forum Discussion

Blayke's avatar
Blayke
Copper Contributor
Feb 20, 2023

Highlight Cell if the value does not appear in another worksheet

I need it to highlight red for the below scenario.

Sheets 3 – ‘Column B’ and Sheet 4 – ‘Column A’ do not match a value in sheet 2 ‘Column J’

 

I am currently using this for it to flag green if sheet 2 matches in sheet 4
=NOT(ISERROR(VLOOKUP($J1:$J500,'sheet 4!$A$1:$A$427,1,FALSE)))

However, cannot get it work to highlight red on sheet 4 if the value doesn’t appear in sheet 2

excel
Formulas and Functions
office 365

2 Replies

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Feb 21, 2023

    Blayke 

    Example: If the value is not found in Sheet1, range A1:A3 then in Sheet2 E1 Red.

     

    =COUNTIF(Tabelle1!$A$1:$A$3,E1)=0

    Paste the formula into conditional formatting

     

    Example file included.

     

    Hope I was able to help you with this info.

    NikolinoDE

     

     

    • Blayke's avatar
      Blayke
      Copper Contributor
      Feb 22, 2023

      NikolinoDE 

      So I've got it running, but it keeps highlighting blank cells red as well, what's the best way to fix it? - 

       

      Also do you know how to have a running count of how many cells are red where the count will appear on sheet 1, example below?

       

Resources