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

Nia Phillips

New Member
Joined
Oct 30, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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
 

Attachments

  • Untitled.png
    Untitled.png
    79.1 KB · Views: 2
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top