Conditional formatting within dependent drop down lists

**Jonesy1989** · 10-11-2012, 05:07 AM

Hi there,

I had a look through the site and couldn't find an answer so here it goes (sorry if i use the wrong terminology, I'll try to describe things as best I can):

I have a spreadsheet with dependent drop down lists where I can choose from a list of 10 in the 1st cell. Then depending on what is selected in that cell will then give a different list in the second cell macthing the seelction in the first. Now what I want to implement is a way to indicate when the selection in the second list does not match up with the first, i.e. you make a selection in the second list but then the selection in the first list is changed meaning that the 2nd selection doesn't correspond to the 1st. I tried to use a combination of if, not and or functions to carry this out, which worked but there is a limit of 3 condtions per cell.

The indication can be anything: highlight cell, change text colour, textbox etc. as long as it is clear that there is an error. Is there a way to use VBA to do this or a different way to do it using conditional formatting?

Cheers

**Pete_UK** · 10-11-2012, 05:48 AM

Post a sample workbook and I'll take a look at it for you.

Pete

**Jonesy1989** · 10-11-2012, 05:58 AM

There we go that should be attached. Columns G and H have the dependent drop down lists, and I want H to somehow recognise when the selection in H doesn't match the list specificaed in G.

Cheers

**Pete_UK** · 10-11-2012, 06:23 AM

Okay, I've set this up using the named ranges which are shown in yellow, so I've used INDIRECT for the dependent lists. Then the conditional formatting applied to H3:H5 is using this formula:

=ISNA(MATCH(H3,INDIRECT(G3),0))

to set it to a red background. Change any of the errors in column G to see the effect.

Hope this helps.

Pete