If ActiveCell

I'm trying to create a macro which will change the fill color of a cell based on text displayed in a cell. If the text displayed is Red, I'd like the cell fill color to be red, if text is yellow change the cell fill color to yellow, if green change the color to green. Below is what I've written. The code currently is changing the cell fill color to red no matter the value. Can someone assist?

Sub SetActive()

If ActiveCell = "Red" Then
ActiveCell.Interior.Color = 255 ' Color cell interior red
ElseIf ActiveCell = "Yellow" Then
ActiveCell.Interior.Color = 49407 ' Color cell interior orange
ElseIf ActiveCell = "Green" Then
ActiveCell.Interior.Color = 65280 ' Color cell interior green
End If

End Sub

Are you using Conditional Formatting to change the color in the cell?

Maybe:

Please Login or Register  to view this content.

vaw30080 beware of case sensitivity. If your code contains = "Red" and you type red or RED in a cell your code will not work.

Using Select Case LCase(ActiveCell.Value) and then typing your code as "red" or "green" will change this.

Modified taking into account skywriter's comments.

Please Login or Register  to view this content.

Also as mention by stnkynts if you are using conditional formatting for the cells, please let us know the formulas so that adjustments can be made.

Okay. Thanks for the many replies. The 'Case Is' and the 'LCase' is working, however, I have to manually run the code to enact changes when the value in the cell is changed. How do I get vba to automatically run when a value is input or the existing value is changed in the active cell?

Place it within the appropriate sheet module

Please Login or Register  to view this content.

Stnkynts, That's it. Works like a charm. Thanks!