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!

 

[Marcelo Branco]

Hi,

Using your second example try something like this

Sub Worksheet_Calculate()
'Set Font Color based on Target cell's contents
Dim R As Range

For Each R In Range("A7:G7")
With R
Select Case .Value
Case "Red"
Font.ColorIndex = 3
'and so on
End Select
End With
Next
End Sub

HTH

M.

 

[river]

Thank you Marco, but I didn't provide enough information. The text of the cell is pulled from another worksheet within the workbook containing a table array used as a VLOOKUP function. I had to do it for each cell (D18, then E18 in the next field. Fortunately there were only 7 of the below blocks of commands. Ugly but it worked.

Sub Worksheet_Calculate()
'Set Interior Color based on Target cell's contents
Dim R As Range
'For Each R In Range("D18")
If Range("D18").Value = "Yellow" Then Range("D18").Interior.ColorIndex = 6
If Range("D18").Value = "Black" Then Range("D18").Interior.ColorIndex = 48
If Range("D18").Value = "Green" Then Range("D18").Interior.ColorIndex = 4
If Range("D18").Value = "Red" Then Range("D18").Interior.ColorIndex = 3
If Range("D18").Value = "Varies" Then Range("D18").Interior.ColorIndex = xlNone
If Range("D18").Value = "Ineligible" Then Range("D18").Interior.ColorIndex = xlNone
If Range("D18").Value = "" Then Range("D18").Interior.ColorIndex = xlNone