Search list a highlight non match

**jeffreybrown** · 10-31-2009, 11:06 AM

Within conditional formatting I use =COUNTIF(Task,I4)=0 and it highlights the non-matches. Task is a named range which works fine, but how could I actually just use a string instead of the named range? The named range only consists of Major and Sub. My first guess would be =COUNTIF({"Major";"Sub"},I4)=0 but this is not working. Any thoughts?

**Ron Coderre** · 10-31-2009, 11:41 AM

Originally Posted by rocky1

Within conditional formatting I use =COUNTIF(Task,I4)=0 and it highlights the non-matches. Task is a named range which works fine, but how could I actually just use a string instead of the named range? The named range only consists of Major and Sub. My first guess would be =COUNTIF({"Major";"Sub"},I4)=0 but this is not working. Any thoughts?

Using Excel 2007 or Excel 2003....
Try this:

Please Login or Register  to view this content.

Does that help?

**jeffreybrown** · 10-31-2009, 11:46 AM

Hi Ron,

I tried the {} before, but still get an error, You may not use unions, intersections, or arrays constants in Conditional Formatting criteria.

**Ron Coderre** · 10-31-2009, 11:53 AM

Originally Posted by rocky1

Hi Ron,

I tried the {} before, but still get an error, You may not use unions, intersections, or arrays constants in Conditional Formatting criteria.

Oops! I neglected to allow for CF.

Try this:

Please Login or Register  to view this content.

Is that better?

**jeffreybrown** · 10-31-2009, 12:03 PM

There I go again overlooking the obvious and trying to make things too hard.

Thanks Ron, but I will make one modification because I only want in to highlight the non-match...=NOT(OR(I4="Major",I4="Minor"))