conditionally format range based on cell value

I need to conditionally format the range I4:I40 so that;

If any cell within this range is equal to any of the values in the yellow cells in column H then they will be shaded red. If they are not equal they will be shaded green.

note; the cells highlighted yellow in column H have only been highlighted to demonstrate the cells to look against. They will always be in the same locations but will not necessarily be highlighted yellow. the values of these cells will change.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
I have set the colour of the range [I4:I40] to 'green', but set Conditional Formatting so that the following condition will change the cells in that range to 'red':

=OR(I4=H$2, I4=H$42, I4=H$48, I4=H$54, I4=H$60, I4=H$66, I4=H$72, I4=H$78, I4=H$84, I4=H$90)                  

Applying the same condition to the entire range will change the test against cell [I4] to the respective cells within the remainder of the range selected.

Please see the attached workbook.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I have created some Named Ranges like H_1, H_2........H_9 for the yellow cells in col. H.

Then use the following formulas to make a New Rule for conditional formatting for the range I4:I40 and set the format as per your choice.

For Green

Open in new window

For Red

Open in new window

For details please refer to the attached workbook.
mikes6058Author Commented:
Sorry I think I may have failed to mention an important condition.

It appears as if both of you have removed the drop down lists in the yellow cells in column H. These need to remain so that the values in these can be changed by the end user.

for some reason its not allowing me to add the drop down lists back in???

Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
That's strange. Drop down list in col. H is working perfectly in the workbook I attached with my reply.
I cross checked by downloading my file and its working without an issue.

You may change the drop down value in col. H.
[ fanpages ]IT Services ConsultantCommented:
Hi Mike,

Did you add the drop-down lists between listing your question initially, then deleting it, & re-posting?

I was already working on a solution when you deleted the first question.

Sorry, I had not noticed a change in the two workbooks you posted.

I have now applied the Conditional Formatting to the workbook at the top of this question thread.

A new attachment is below for your further review.
mikes6058Author Commented:
I got to the botton of the dropdown problem. I created and uploaded the file from 2010 I then downloaded your files from a different machine using 2007.

All sorted now.

Great work guys

[ fanpages ]IT Services ConsultantCommented:
Thanks Mike :)
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Mike!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.