Turn cell a color if cell content does not equal cell content of a range of values on another sheet

    Hello wonderful experts! Apologies that my header was long - I'm trying to accurately describe my problem.


    I'd like for any cell to turn orange upon entry (Sheet1, A2:A17) if the data entered there does not equal a project on the list shown on Sheet2.


    For example, if they enter just the number "5" in A2 on Sheet1, it would turn orange because that isn't an offered project code on Sheet2. If they entered "402Plan" it would stay white, because it IS on the list on Sheet2. I thought at one point I had it figured correctly but it may have only worked for the same corresponding cell, and I need it to look at the whole range on Sheet2. They also have the option of leaving it blank, with no project Code on Sheet1. I hope that makes sense. I've tried a lot of searching but can't seem to find a clear solution for this assumed conditional formatting. Thank you in advance!


    I attached a much smaller example of what I referenced above.

    Apologies, my last post didn't quite take into consideration that the entire project list was not being taken for a match but rather just the equivalent row


    This formula in the conditional formatting "=IF(SUM(COUNTIF(A2,ProjectCode)),"FALSE","TRUE")", will work for the whole project list provided you set your project code list as a dynamic named list with this formula "=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A),1)" in the named range menu

    Here -

    $A$2 is the starting code (in sample it was 2nd row, A Column)
    $A:$A is the column containing your Project Code List


    Conditional Formatting Formula explanation is as follows -

    A2 - is the first cell to add into conditional formatting

    ProjectCode - is the name of the Named range that I have instructed you to make earlier
    IF formula is reversing the return that the SUM and CountIF formula is returning, if you don't add this then Conditional formatting will colour the matching results and not the entries that don't match as you requested

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login