Conditional Formatting without 'Fill'

I've created a table and in that table is a column that has a data validation list for users to select from. I didn't want an error message to pop up if someone typed something different in because there are custom cases, but I did want the text to be red if they were to type something different than my list's range.

I simply went to conditional format and set [Formatting is true when cell value <> Range]. And so when I put in a value not within my defined range, the text is red. The only problem is so is the fill of the cell. My table has alternating colored lines (white and gray) and I'd like to maintain that by the conditional formatting only changing the text color and nothing else.

Any tips?
Thanks!

*************************************

SOLVED:

Originally Posted by Ace_XL

I changed the formula to
=NOT(ISNUMBER(MATCH(F6,PrimaryVendor,0))) to find exact matches

The formula now checks for an exact 'Match' based on the input in the active cell (F6 in your first case) with your 'PrimaryVendor' list . Note the relative reference to F6, this will change to F7, F8 and so on for the next rows. If it finds a match it will return a number which will be identified as a TRUE by the ISNUMBER function, the NOT at the beginning will convert this TRUE to a FALSE and the condition will not be met, thereby not changing the format of the cell.

However, if it does not find a match, the ISNUMBER function will return a FALSE which will then be converted to a TRUE and the format will change.

It does not work on a random basis as the format will change only for values that are not found in your PrimaryVendor list.

Hope this helps! See attached.

This solved my fill issue as well. Thanks for the tips guys - I appreciate it.

In the conditional formatting dialog box, make sure you have the Fill set to 'No Color'. It is a big button above the colors (at least on my Excel 2007). I tried out a sample and it worked as expected.

Pauley

I checked that before I even posted here, but for some reason I think it takes no fill as in there's no fill for this cell. I don't want that as it removes the fill that was previously in the cell (gray).

Here's an example: Engineering Release.xls

Attached is what I see when I open it. I believe this is what you are expecting, too.

What happens if you refresh your sheet (F9)? Do you have access to another computer to test it out?

Something to try - get out of pagebreak view and switch to normal. Also, go to 100% zoom.

Originally Posted by Pauleyb

Attached is what I see when I open it. I believe this is what you are expecting, too.

What happens if you refresh your sheet (F9)? Do you have access to another computer to test it out?

That is what I am expecting, but alas it doesn't appear that way for me. And refreshing does nothing.

Originally Posted by Pauleyb

Something to try - get out of pagebreak view and switch to normal. Also, go to 100% zoom.

As inconvenient as that would be, I would live with it, but that didn't change anything either.

Originally Posted by bentleybob

You've somehow deleted the manual fill from that cell. Use the format painter to copy it from another cell (like F8).

When I used the format painter to copy from another cell it removed the conditional formatting.

And here's a picture of mine for proof (haha): WhiteCellRedText.PNG

I have this question open in another thread, but do you think it has anything to do with those values not actually being the ones I want to be red? Everything on that drop down list should be black text with the default fill for that specific cell and anything else (let's say I type abc in the cell) should be red. My conditional formatting formula appears to be correct, but it's doing this strange thing for me.

Thanks for the suggestions guys.