Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

change cell fill color if A! says yes

0

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

Answer
Discuss

Answers

0
Selected Answer

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.

=$A$1="plastic"

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.

=EXACT($A$1,"PLasTIc")

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

Discuss

Discussion

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 (rep: 2) Dec 23, '19 at 7:20 am
Hello Variatus
When I use conditional formatting I always set the "Applies To" after setting the formatting and have not encountered the glitch you describe. Although doubtful, it may be possible that you thought you selected D1:D4 but actually only had D1:D2 selected.
One other thing I do is press "F2" before entering in the formula field and when editing the "Applies To" field - this allows the field to be edited just like a cell.
WillieD24 (rep: 537) Aug 23, '20 at 11:41 am
Add to Discussion


Answer the Question

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