Conditional Formatting of List based on selections from Dropdown list

[FoxyPinkChick]

Hi, I was hoping someone could help me. I have two worksheets. In Sheet 1 I have created a list called "List" (original I know). This contains ten items.

In Sheet 2, I have a number of cells that are a dropdown of this list using Data Validation to create it.

At some point I need to have all items from the list selected somewhere in Sheet 2. I want to format (for example turn green) each of the items on the list in Sheet 1 when they are selected anywhere in Sheet 2. Conditional formatting does not allow me to reference multiple cells. Any way to do this?

[Pepe Le Mokko]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Brumbot]

OK. So create a named range for all of the cells with the dropdown list in, such as "Dropdowns", or something. Then, say the first value in "List" is in A1, select A1 and open the conditional format box.

Enter:

Please Login or Register  to view this content.

into the custom formula box. Format as you want (e.g. green).

Use the format painter to copy the formula to the other cells in your "List". That should work! HTH.

[FoxyPinkChick]

Thanks! This worked perfectly. I knew it was simple, I was missing the creation of the range. Thanks a million.

[Brumbot]

Thanks! This worked perfectly. I knew it was simple, I was missing the creation of the range. Thanks a million.

That's quite alright. Older versions of excel don't allow you to reference other sheets in conditional formatting and so you need to create a named range to refer to these ranges.

If you feel like you query has been answered, please mark the thread as closed using the thread tools button and don't forget to leave feedback!