change cell fill color if A! says yes


I have an excel pricing sheet for my busines.

Many options are on the sheet and i sometimes i skip over rows i should have filled out.

I'd like cells d1 thru d4 to change fill color based on text in a1.

if a1 text is  "plastic" then the fill in cells d1 thru d4 should turn red.

Thnk you, Roy




Select D1:D4 and press Conditional Formatting on the Ribbon's Home tab. Click New Rule. Select "Use a formula to determine which cells to format". Enter this formula.


Then set a red Fill as the desired format and conclude with clicks on the OK buttons that are offered.

There are other ways of doing this and when I tried the above on my laptop only D1:D2 changed to red. I have no idea why this happened, and I couldn't make the computer repeat the mistake. However, here is the remedy.

After setting the format, select Conditional Formatting > Manage Rules. Select the rule from the list and click Edit Rule. In the above example the Applies To field correctly showed =$D$1:$D$4. I changed this to =$D$1:$D$8, confirmed, and repeated to sequence to change it back to =$D$1:$D$4. Thereafter the format was applied correctly. I'll be interested, if anyone knows why Excel needed to be nudged.

However, having come this far, I selected B1 (at random) and set the format only for that cell. Then, in Manage Rules, I changed the Applies To field to =$F$2:$F$12, and that worked as expected. The point was to demonstrate that the cells to be formatted are controlled by the address in the Applies To field, and while the address is determined by the selection initially it can be changed independently later.

Incidentally, the above formula checks A1 insensitive to case, meaning the format will be applied if A1 contains the characters p-l-a-s-t-i-c, in that sequence, irrespective of their case. "PLasTIc" will trigger the format. If you need the format to be applied more discriminatingly, use this formula.


My suggestion is rather impractical but the formula can be used to enforce capitalisation, for example.



thank you. i read your answer twice , and a 3rd time slowly. I'll do a step by step and get back with you. Thank you again for your help. Roy
Roy Shimp Dec 23, '19 at 7:20 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login