CODEWITHSUNDEEP
excelvba
Mikayla Baer
Mikayla Baer

Reputation: 49

Apply colors to a cell based on text in another cell

I don’t want to use conditional formatting because I need a different value as the text in the cell (not what the color values apply to).

Column E is status (at risk/on track/needs improvement)
Column I is trend, displayed as arrows (Unicode text)
Column J is blank

I would like the color of the corresponding cells to be based on Column E text “Needs Improvement” in red, “At Risk” in yellow, and “On Track” in green.

In J2 through J13, I need the color only from the E2:E13 status and the trend arrows from I2:I13.

This has been asked before in one way or another, but I can’t figure out the conditional formatting in VBA.

Conditional formatting doesn’t work, I tried to pull conditional colors from column E, but the code couldn’t integrate conditional formatting colors (it had to be true cell colors).

Upvotes: 0

Views: 915

Answers (1)

Cameron Critchlow
Cameron Critchlow

Reputation: 1827

Below are the 4 sets of conditional formatting I used to accomplish the below example. I did not know what your trend data looks like so you'll need to adjust that to fit. To note, the formula in column J is ["=I" & row], so it's pulling over the same values. Those are hidden with the icon formatting. ::

Where RG = range("J2:J18")

Colour:="Red"     while [=$E2="At Risk"] 
Colour:="Yellow"  while [=$E2="Needs Improvement"]
Colour:="Green"   while [=$E2="On Track"] 
Shape:="Icon Set" [checkmark] Show Icon Only

Example of formatting:
enter image description here

Example of result:
enter image description here

Let me know if any of that doesn't make sense.

Upvotes: 3

Related Questions