Change cell font color to match text but is based on a formula

[river]

I have a workbook with two spreadsheets. One has a series of cells (say, A7:G7) that return a word ("Red", "Yellow", "Green", "Blue") based on a lookup table in the other worksheet with an array defined spanning (J2:L1006) that contains the words. The table array may be irrelevant. A dropdown option on the first spreadsheet determines the row in the array used to populate A7 to G7.

"All" I want to do is write vba code that will make the font color in A7:G7 match the word (e.g., "Red" will be in a red font, etc).

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value = "Green" Then
Target.Font.ColorIndex = 4
End Sub
Doesn't seem to work. Neither does
Private Sub Worksheet_Change(ByVal Target As Range)
'Set Font Color based on Target cell's contents
Dim R As Range
For Each R In Range("J2:L1006")
With R
Select Case .Value
Case "Red"
Font.ColorIndex = 3
'and so on
End Select
End With
Next
End Sub
Help please!

 

[AlphaFrog]

If "Sheet1" Cell A1 has your dropdown list (the trigger event).

And "Sheet2" has the range you want to set the font.color

Put something like this in the "Sheet1" module.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = [COLOR="Red"]"$A$1"[/COLOR] Then
    
        'Set Font Color based on cell's contents
        Dim R      As Range
        For Each R In Sheets([COLOR="Red"]"Sheet2"[/COLOR]).Range("J2:L1006")
            With R
                Select Case .Value
                    Case "Red"
                        Font.ColorIndex = 3
                        'and so on
                End Select
            End With
        Next
    
    End If
    
End Sub

If you have Excel 2007 or later, you could do this with just Conditional Formatting.