Conditional Formatting in Excel

Question

I'm very new to Excel and VBA and was wondering if there is a way I could make conditional formatting based on values in a drop down list (created from data validation).

I currently have a warning if the user enters something that is not valid (data validation), but I want to change the cell's background color to red if invalid, or green if valid.

Again the options I want to test against are in the data validation created drop down list.

Answer 1

Go to the Format menu, select "Condition Formatting..."

You can set formulas there for any condition.

Answer 2

The easiest way to do this is to create the list in cells somewhere on your sheet. Then you use the named range ability to give it a name, let's say for this example ValidList. You give it the name by selecting the list, then go to the Insert menu and choose Name->Define, then enter the name and hit ok.

You then go to the cell in questions (we'll use A1 for this example), select it, then go to the data validation menu. When you choose the list option, enter the following for the Source:

=ValidList

Then having the cell selected you go to conditional formatting, choose the formula option, then enter the following formula, and your desired result:

=ISERROR(MATCH(A1, ValidList, 0)

Note, that the only time this will really help you is if someone does a 'Paste Special' on a forbidden value, since otherwise data validation will catch the error (unless you're still allowing them to enter incorrect data).