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

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.

 

[Nia Phillips]

Hi, we are having some difficulty with this conditional format on one column. The format is =AND(Q2="",SEARCH("|"&F1&"|","|FNC|Urgent FNC Upgrade|CHC|Remains Residential|FNC Respite|CHC Respite|S117|High Cost|H&SC|Cease to fund from MDT|"))

This is highlighted for column Q and pulls the category from column F, so that is correct. However, it doesn't seem to be highlighting the correct cells. The cells without text should be highlighted, but are not highlighted correctly. The cells with text, shouldn't be highlighted, but are. Hoping someone can help me with this please Please see attached image

 

[Peter_SSs]

It would help if you gave us any sample data in a form we can easily copy to test with. We cannot do that with an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Having said that, it looks like this is an issue with your formula
=AND(Q2="",SEARCH("|"&F1&"|","|FNC|Urgent FNC Upgrade|CHC|Remains Residential|FNC Respite|CHC Respite|S117|High Cost|H&SC|Cease to fund from MDT|"))

The formula is looking for column Q to blank in a row but for one of those texts to be found in column F in the row above, not on the same row. Is that the issue?

We cannot see the row numbers in your image but when you select a range to apply the conditional formatting to, your formula should be written in relation to the active cell row, usually the first cell in the selection. Maybe reviewing post 7 might help.

 

[Nia Phillips]

It now seems to be working from changing Q2 to Q1. So the Q & F match. Thank you

It would help if you gave us any sample data in a form we can easily copy to test with. We cannot do that with an image.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Having said that, it looks like this is an issue with your formula
=AND(Q2="",SEARCH("|"&F1&"|","|FNC|Urgent FNC Upgrade|CHC|Remains Residential|FNC Respite|CHC Respite|S117|High Cost|H&SC|Cease to fund from MDT|"))

The formula is looking for column Q to blank in a row but for one of those texts to be found in column F in the row above, not on the same row. Is that the issue?

We cannot see the row numbers in your image but when you select a range to apply the conditional formatting to, your formula should be written in relation to the active cell row, usually the first cell in the selection. Maybe reviewing post 7 might help.

 

[Peter_SSs]

Great. Thanks for letting us know.