Fill a row with color using Conditional Formatting + Data Validation

Question

Hi all,

I'm looking for conditional formatting to fill a row a certain color once a specific text is chosen in a Data Validation drop down cell (List). I have multiple choices and would like to set up different colors for each.

Would anyone be able to help?

Answer 1

Hi Samuel. Sorry to be so long getting back. I was out of pocket a couple of days. Here is the way to format your whole line.

1. Select all of the cells to be formatted. In the screenshot below I used C3:D10.
2. Open Conditional Formatting from the Home ribbon. Select New Rule>Use a Formula.
3. Enter this formula in the formula line:

=$C3="IN PROGRESS - OPS"

In your screenshot above, your first dropdown selection was in C3 so that is what I used in the formula. Make sure that the first Row of the selection you make in #1 above (Row 3) matches the row of the reference in the formula (Row 3).

4. Click on the Format button in the lower right of the format window and select the color you want for this particular choice on the Fill tab of the Format Cells dialogue box. Then click OK and OK.

.

In this formula I used "IN PROGRESS - OPS". You will need to repeat steps 1-4 for each choice making a new rule for each of the choices in the dropdown replacing the text in quotes each time with the next option.

NOTE: What you type inside the quotes match the same spacing and characters as your dropdown.

Answer 2

Hi Samuel. I am n Excel user like you.

Select all of the cells that you want evaluated for the Conditional Formatting. On the Home ribbon click on Conditional Formatting>Highlight Cell Rules>Text That Contains. In the line enter the text that you want to look for. Then, in the dropdown for the formatting select Custom Format which will open the Format Cells Dialogue box and you can select whatever color you want to go with each choice.

Repeat this for each of the possible choices in your dropdown.

Answer 3

Thanks for your help,

Unfortunately it only highlights the cell itself where the specific text is located - how do I make it highlight the row?

Apologies, I'm not an expert with Excel!

Answer 4

That's done it,

Thank you so much for your help!