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.

Do you mean that you want the conditional formatting to compare the value to the values in the List box? or do you mean you want the actual List box to change color? — Lance Roberts
The First. I want the conditional to compare the value to the values in the listBox — littlevahn
I'm still working on this. How do you populate the Listbox? Do you use the ListFillRange property or something else? — Lance Roberts
I use the data validation tool to fill the list. I realize its back practice but im just inputting the strings I need for the list: (ie. Male,Female,Boys,Girls,Infant,None). Currently I have just been making individual rules for each list box string but obviously that's not very productive. — littlevahn
Ok, that isn't a Listbox, I'll modify the question and dig some more. — Lance Roberts

Lance Roberts Lance Roberts · Accepted Answer · 2010-05-12T21:04:30

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).

Conditional Formatting in Excel

2 Answers