Conditional Formatting on cells that DO NOT contain specific text

[NJS1982]

Hi all, been a while since my last query, so I hope that somebody can help!

I basically a list of different report names within a cell, the full list here:

Project Start Form
6 Month Report
12 Month Report
18 Month Report
24 Month Report
30 Month Report
36 Month Report
42 Month Report
48 Month Report
54 Month Report
60 Month Report
No Further Reports​

Is there a way, using Conditional Formatting of something, to highlight another cell completely if the return in the cell that should contain the other contains something else, i.e. 37 Month Report, or 31 Month Report. In summary, these kind of entries should not exist and when they appear, I want to flag them elsewhere in the sheet!?

Thanks!

 

[etaf]

Re: Contiional Formatting on cells that DO NOT contain specific text

yes you can do that with conditional formatting
use a formula
=ISERROR(INDEX(Sheet2!$A$2:$A$12,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$12,0)))
and on sheet 2 have your list of correct results

for 2007, 2010 or 2013 excel version
Conditional Formatting


Highlight applicable range >>

Select the range you want to highlight - The formula has to start at the same row



Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:



=ISERROR(INDEX(Sheet2!$A$2:$A$12,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$12,0)))



Format… [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK