Excel VBA - Color Code Column cells based on values

Closed
jdmurphy Posts 1 Registration date Monday December 22, 2014 Status Member Last seen December 22, 2014 - Dec 22, 2014 at 10:20 AM
TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 - Dec 23, 2014 at 11:25 AM
Hi,

Super new to VBA, and need to write code to evaluate the values in Column I, starting with I3 and going down a varying number of rows depending on the report since it has new data each time I pull it. I need the cells to be red if they are > 2.99, yellow if they are in between 1.5 and 2.989, and green if they are below 1.5. I kind of get the 2 ends of that argument, but creating an argument for the "in-between" option I can't figure out. Any help on the loop/if statements I can use to color code this column?

1 reply

TrowaD Posts 2901 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 525
Dec 23, 2014 at 11:25 AM
Hi Jdmurphy,

No need for VBA here.

Select cell I3, go to start ribbon > conditional format > manage rules > new rule.

1) =I3<1.5 --> format Green
2) =AND(I3>=1.5,I3<=2.989) --> format Yellow
3) =I3>2.989 --> format Red

Don't forget to apply the format.

Now drag cell I3 down as far as needed applying format only.

When you don't want empty cells to be green then change the first rule into:
=AND(I3>0,I3<1.5) --> format Green

I took the liberty to alter the numbers and symbols a bit to prevent gabs in the format.

Best regards,
Trowa
0