VBA + Conditional Formatting

[Nanogirl21]

Can someone please help with a VBA to do the following


(1) Clear conditional formatting from worksheet titled Tracking


(2) Apply conditional formatting for all rows in column O with the below rules
a. change cell background to LIGHT GREEN if formula is true

 =OR(T1="APPLE", T1="BANANA", T1="APPLE / HOLD", T1="BANANA / HOLD")

b. change cell background to ORANGE if formula is true

 =SEARCH("HOLD",T1)

c. change cell background to RED if formula is true

 =AND(OR($L1="GRAPES",$L1="CUCUMBER",$L1="TOMATO"),$O1<10)

Notes
-I’d like to use conditional formatting because I need the cell colors to change when making updates throughout the day.
-I don’t want to apply the rules manually because there are a lot and they would need to be changed often. It’s easier to create a VBA code for each set of rules I’d like to apply.
-If there is any way to make the formulas I used simpler I am all for it!
-T1 and L1 would change to T2, T3, T4, T5 and L2, L3, L4, L5 ect since the rules will be applied to the entire column. I don’t have an exact amount of rows because the data is constantly growing.
- It would be great if I could add a HEX code or RGB for the color i'd like the cell background to.


Thank you.

 

[Leith Ross]

Hello Nanogirl21,

Personally, I stay away from using VBA to setup Data Validation because of the coding needed to do it. Also, users often prefer to keep workbooks macro free whenever possible. Data validation can be accomplished in other ways when using VBA but it really depends largely on your data, layout, and needs.