How to get a row of Columns to change colour when something is selected from a drop down box

[Nia Phillips]

I have a row of columns and when I choose a Fast Track, from a drop down, I would like row C, D and F to change to a colour so I know to fill them in

 

[Joe4]

Welcome to the Board!

You should be able to do this with Conditional Formatting, choosing the "Use a formula to determine which cells to format" option.

Your formula would look something like this for row 1, if your drop-down box appears in cell A1:

=$A1="Fast Track"

 

[Nia Phillips]

I have a row of columns and when I choose a Fast Track, from a drop down, I would like row C, D and F to change to a colour so I know to fill them in

These are the only options I have for conditional formatting.

 

[Peter_SSs]

These are the only options I have for conditional formatting.

After selecting your cells in, say, column C (not row as rows run across the worksheet) from that list choose New Rule .. and then what Joe suggested.

 

[Nia Phillips]

After selecting your cells in, say, column C (not row as rows run across the worksheet) from that list choose New Rule .. and then what Joe suggested.

View attachment 119130

Thank you. I have managed to do some of it. Is there a way to stop the row F1 changing colour? So it starts from F2 down? I have tried changing F1 to F2, but then the highlighted columns jump a line, if that makes sense. Photo is attached. Also, if I want the column to change colour from multiple drop down's such as Fast Track, FNC, CHC, how do I do this? I have tried adding a comma but it doesn't allow the formula to work.

 

[Nia Phillips]

Thank you. I have managed to do some of it. Is there a way to stop the row F1 changing colour? So it starts from F2 down? I have tried changing F1 to F2, but then the highlighted columns jump a line, if that makes sense. Photo is attached. Also, if I want the column to change colour from multiple drop down's such as Fast Track, FNC, CHC, how do I do this? I have tried adding a comma but it doesn't allow the formula to work.

 

[Peter_SSs]

if I want the column to change colour from multiple drop down's such as Fast Track, FNC, CHC, how do I do this?

Let's deal with that first.
Select column L again
Conditional Formatting -> Manage Rules..
Select the existing rule then choose Edit Rule ..
Change the formula to this (add more values if required)
=SEARCH("|"&F1&"|","|FNC|CHC|Fast Track|")

Is there a way to stop the row F1 changing colour?

Once the above is in place
Select cell L1 only
Conditional formatting -> Clear Rules -> Clear Rules from Selected Cells

 

[Nia Phillips]

Let's deal with that first.
Select column L again
Conditional Formatting -> Manage Rules..
Select the existing rule then choose Edit Rule ..
Change the formula to this (add more values if required)
=SEARCH("|"&F1&"|","|FNC|CHC|Fast Track|")


Once the above is in place
Select cell L1 only
Conditional formatting -> Clear Rules -> Clear Rules from Selected Cells

Thank you. That's now working. Is there a way that once the columns are filled in with text, for example column L, it has a date date or any text in there, that the colour can be reomved?

 

[Peter_SSs]

Is there a way that once the columns are filled in with text, for example column L, it has a date date or any text in there, that the colour can be reomved?

Sure

1. Select cell l2
2. Conditional Formatting -> manage rules ..
3. Click on the rule and choose 'Edit Rule ..'
4. Paste this formula over the top of the existing one =AND(L2="",SEARCH("|"&F2&"|","|FNC|CHC|Fast Track|"))
5. Click OK -> Apply -> OK

 

[Nia Phillips]

Sure

1. Select cell l2
2. Conditional Formatting -> manage rules ..
3. Click on the rule and choose 'Edit Rule ..'
4. Paste this formula over the top of the existing one =AND(L2="",SEARCH("|"&F2&"|","|FNC|CHC|Fast Track|"))
5. Click OK -> Apply -> OK

That's brilliant! Thanks so much for all your help