Highlight cells based on dropdown selection

Yes, it can; check the attached file, please.

Say that your named list is in cells A1:A12.

First row with drop down list starts in cell B15, and captions are in cells C14 and rightwards. Then in cell C15 do this: Conditional formatting - New rule - Use a formula to determine which cell to format; then in the field type formula

$B15=C$14

and set format (in this case, I coloured cell in yellow). Then copy it right and down accross the range.

So, if selected dropdown option is equal to column caption, respective cell will turn yellow. Use semi-absolute references to be able to copy the cell right and down; i.e., if you type, as Excel would turn it, $B$15=$C$14, then you could not copy it, so manually switch to semi-absolute references if Excel enters absolute ones.

Exactly what I was looking for. Thanks!

Hi there this is exaclty what i'm looking to do, how did you code for when a selection was made, that it would highlight a certain colum cell?

Originally Posted by froment

Yes, it can; check the attached file, please.

Say that your named list is in cells A1:A12.

First row with drop down list starts in cell B15, and captions are in cells C14 and rightwards. Then in cell C15 do this: Conditional formatting - New rule - Use a formula to determine which cell to format; then in the field type formula

$B15=C$14

and set format (in this case, I coloured cell in yellow). Then copy it right and down accross the range.

So, if selected dropdown option is equal to column caption, respective cell will turn yellow. Use semi-absolute references to be able to copy the cell right and down; i.e., if you type, as Excel would turn it, $B$15=$C$14, then you could not copy it, so manually switch to semi-absolute references if Excel enters absolute ones.

Originally Posted by kevinthien

Hi there this is exaclty what i'm looking to do, how did you code for when a selection was made, that it would highlight a certain colum cell?

It's just the conditional formatting. In the example file I attached to the post above, captions are in row 14, and they are compared to value from drop-down menu in column B; if they match, then the cell is highlighted.

To accomplish that in, say, cell C15 (the first cell in range), select it (I mean, position a cursor there), then go to Conditional Formatting - New Rule; select "Use a formula to determine which cells to format", and in box below that, ""Format values where this formula is true" type this condition:

=$B15=C$14

Then click button "Format", choose the desired formatting and confirm it by clicking OK untill you exit the dialogue boxes.

If you choose value from drop-down menu in cell B15 which is equal to caption in cell C14, it will get highlighted.

Now, you see the references above are semi-absolute: column B and row 14 are absolutely referenced, which allows you to simply copy-paste this cell to desired range; if you copy it to the right, it will become

=$B15=D$14

so it will compare drop-down menu in cell B15 with caption in column D, and if you copy it down, it becomes

=$B16=C$14

so now drop-dwon menu in cell B16 is relevant, while column caption is still in cell C14.

Hope it helps.

Is there a way to do this in columns rather than rows? Doesn't seem to work when i do this in columns.

Hi, I need help! How can I highlight selected cells depending on the option on the dropdown list? Is this possible in conditional formatting?

Hi, I need help! How can I highlight selected cells depending on the option on the dropdown list? Is this possible in conditional formatting?

Originally Posted by froment

Yes, it can; check the attached file, please.

Say that your named list is in cells A1:A12.

First row with drop down list starts in cell B15, and captions are in cells C14 and rightwards. Then in cell C15 do this: Conditional formatting - New rule - Use a formula to determine which cell to format; then in the field type formula

$B15=C$14

and set format (in this case, I coloured cell in yellow). Then copy it right and down accross the range.

So, if selected dropdown option is equal to column caption, respective cell will turn yellow. Use semi-absolute references to be able to copy the cell right and down; i.e., if you type, as Excel would turn it, $B$15=$C$14, then you could not copy it, so manually switch to semi-absolute references if Excel enters absolute ones.

Hi All,
This works for exact match. What about drop down with different values, columns with different values? Please advise ASAP


Thanks
Jay