I have a worksheet with text values in column A (only about 6 rows). I have text values (or blanks) in the range D1:H7. I want to set conditional formatting on range D1:H7 so that if a value in that range is NOT in my column A values, it is changed to red lettering. (If the cell is blank, it just leaves it alone.) I've tried a bunch of formulas in the conditional formatting from the ribbon, and none of them work. Any ideas? I've uploaded my worksheet.
Need Conditional Formatting formula
- Thread starter glennpc
- Start date
Clear all CF First
Select E1:J6
Conditional Formatting, New Rules, Use a Formula
=countif($A$1:$A$7,E1)<1
Apply a Red Font
Apply
Note that some of the entries in your Table eg: G2 have a leading space
If you want to allow leading or trailing spaces use:
=COUNTIF($A$1:$A$7,TRIM(E1))<1
instead
Select E1:J6
Conditional Formatting, New Rules, Use a Formula
=countif($A$1:$A$7,E1)<1
Apply a Red Font
Apply
Note that some of the entries in your Table eg: G2 have a leading space
If you want to allow leading or trailing spaces use:
=COUNTIF($A$1:$A$7,TRIM(E1))<1
instead
Thanks so much for this Hui! I used the first formula you supplied for my conditional formatting. I eliminated the leading zeros by creating a macro that does a search and replace. I wish the Text-to-Columns didn't add a leading zero when you have a comma delimiter. Anyway, thanks again for your great help!