macro to change font color with drop down list

[ShoshanaM]

I have a listbox from which to choose font color for editing, linked to another cell. This code points to that linked cell to find the color choice and then specifies the font should be that color. It only works sometimes and I cannot figure out why. I put in the msgBox to track the variable. The message box comes up, but the variable value does not. So clearly the variable is not being set properly. Any ideas?

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:z300"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Dim colorchoice As String
Dim color As Integer
'Application.ScreenUpdating = False
Sheets(4).Activate
colorchoice = Range("b1").Value
If colorchoice = RED Then
color = 3
ElseIf colorchoice = BLUE Then
color = 5
ElseIf colorchoice = "GREEN" Then
color = 4
Else: color = 17
End If
Sheets(1).Activate
'Application.ScreenUpdating = True
With Target
.Font.ColorIndex = color
MsgBox "color is" & colorchoice
End With
End If
End Sub

[GaidenFocus]

you don't have quotes around red or blue for starters.

[ShoshanaM]

Yes, that is true. I was experimenting with that part of the code. It doesn't work with or without the quotes.

[GaidenFocus]

I just tested it, it is working for me. maybe post your work book?

[ShoshanaM]

Thanks for trying to help with this. I don't know why it worked for you, but not me. I changed the code that specifies the location of the "colorchoice" variable. It works perfectly now. Thanks again.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1:z300"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
Dim colorchoice As String
Dim color As Integer
colorchoice = Worksheets(4).Range("b1").Value
If colorchoice = "RED" Then
color = 3
ElseIf colorchoice = "BLUE" Then
color = 5
ElseIf colorchoice = "GREEN" Then
color = 4
Else: color = 17
End If
With Target
.Font.ColorIndex = color
End With
End If
End Sub