Conditional Formatting, does not contain text from a range.

[vetrox]

Greeting fellow members. I'm in need of a helping hand when it comes to Conditional Formatting combined with Data Validation.

I have a column with drop list Data Validation based on an array of cells. I'm looking for a way of conditional formatting for that column so that it applies if the cell does not contain a valid input. Basically I want to replace the little data validation "wrong input triangle" with conditional.

In other words, i have a sheet specified for the data validation, called "DataValidation", and I want the conditional formatting on another sheet to apply if the cell does not match it.

This column to be formated

Column A

Input1

Input9

Input3

Input7

Input5

if it doesn't contain any of these entries (this column is on another sheet)

Column A

Input1

Input2

Input3

Input4

Input5

There is over 70 entries in the data validation array.

Hope I explained it well enough. Is this doable?

Thanks up front.

[Pete_UK]

Are you really using XL2003 as your profile states, or have you forgotten to update it? The approach is slightly different with versions before XL2007.

Pete

[vetrox]

Are you really using XL2003 as your profile states, or have you forgotten to update it? The approach is slightly different with versions before XL2007.

Pete

Changed it, I'm using XL2007, and the thing I need to get done is in GoogleSheets, so my conditional formatting options are pretty limited. Although not sure if asking for GoogleSheets related help is forbidden here.

[Pete_UK]

Okay, you need to set up a named range for the 70 words/phrases on Sheet2 using Formulas | Define Range - assume this is called keywords.

Then you should select all the cells in column A on Sheet1 (assuming this starts with cell A1) and click on Conditional Formatting | New Rule | Use a formula... , then enter this formula in the dialogue box:

=ISNA(MATCH(A1,keywords,0))

Click on the Format button then if you want a red foreground click the Color drop-down on the Font tab and choose red, and click on Bold as well. Click OK twice to exit the dialogue box, and Excel will adjust the cell references to suit all the selected cells.

Hope this helps.

Pete

[vetrox]

=ISNA(MATCH(A1,keywords,0))

Took a bit of modification since GoogleSheets requires use of INDIRECT for defined ranges to work, but worked like a charm. Came up with another way to do it in the meantime via ISERROR VLOOKUP.

One more question. How would I make the formula you use skip blank cells?

Thanks a bunch!

EDIT: Nevermind, adding AND(A1<>"") did it. Gave You rep, mod can lock the thread.

[Pete_UK]

Thanks for the rep - glad you got it to work.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Pete