This works great for "direct" changes to the cells.
How can I adapt this to occur whenever recalculation occurs? The cells have
a formula that calculates a span of days to determine the text (RED, BLUE,
YELLOW, ETC). With the code below, when a "source" cell that is an input to
my formula changes, my formula results in new text, but the color doesn't
change as a result.
Thanks.

"T-®ex" wrote:


Hi Kenny! Try this...

Put the ff code for your worksheet (right-click on your sheet's tab,
then click, 'View Code...'):


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetValue As String
TargetValue = UCase(Target.Value)

If TargetValue = "RED" Then
Target.Interior.Color = RGB(255, 0, 0)
ElseIf TargetValue = "YELLOW" Then
Target.Interior.Color = RGB(255, 255, 0)
ElseIf TargetValue = "GREEN" Then
Target.Interior.Color = RGB(0, 255, 0)
ElseIf TargetValue = "BLUE" Then
Target.Interior.Color = RGB(0, 0, 255)
ElseIf TargetValue = "BLACK" Then
Target.Font.Color = RGB(255, 255, 255)
Target.Interior.Color = RGB(0, 0, 0)
ElseIf TargetValue = "GREY" Then
Target.Interior.Color = RGB(127, 127, 127)
ElseIf TargetValue = "PURPLE" Then
'on my comp, both rgb values seem to give same results
Target.Interior.Color = RGB(160, 32, 240) 'from http://www.pitt.edu/~nisg/cis/web/cgi/rgb.html
' Target.Interior.Color = RGB(255, 0, 255)
Else
Target.Font.ColorIndex = xlColorIndexAutomatic
Target.Interior.ColorIndex = xlColorIndexNone
End If
End Sub
--------------------


With the code above, if you type in any cell any of the texts 'Red',
'Yellow', 'Green', 'Blue', 'Black', 'Grey', or 'Purple'
(case-insensitive), the color for that cell will change accordingly...

Kenny Wrote:
I would like to have VB code that will test each cell in an entire
column
(everytime the worksheet is recalculated, if that is not too
time-consuming)
and fill the cell with a different color based upon the string in the
cell.
The possible string values are Red, Yellow, Green, Blue, Black, Grey,
Purple.
The fill color would correspond to the string value.
I can't seem to get the hang of it. Any help would be appreciated.
Thanks in
advance to all the great contributors to this forum.


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread

I have assumed column C as the column you are wanting to check:

Private Sub Worksheet_Calculate()
Dim cell As Range
Dim eRow As Long
eRow = Cells(Rows.Count, 3).End(xlUp).Row
For Each cell In Range(Cells(1, 3), Cells(eRow, 3))
Select Case UCase(cell.Value)
Case "RED"
cell.Interior.Color = vbRed
Case "YELLOW"
cell.Interior.Color = vbYellow
Case "GREEN"
cell.Interior.Color = vbGreen
Case "BLUE"
cell.Interior.Color = vbBlue
Case "BLACK"
cell.Interior.Color = vbBlack
cell.Font.Color = vbWhite
Case "GREY"
cell.Interior.Color = RGB(127, 127, 127)
Case "PURPLE"
cell.Interior.Color = vbMagenta
Case Else
cell.Font.ColorIndex = xlColorIndexAutomatic
cell.Interior.ColorIndex = xlColorIndexNone
End Select
Next
End Sub

Hope this helps
Rowan

Kenny wrote:
This works great for "direct" changes to the cells.
How can I adapt this to occur whenever recalculation occurs? The cells have
a formula that calculates a span of days to determine the text (RED, BLUE,
YELLOW, ETC). With the code below, when a "source" cell that is an input to
my formula changes, my formula results in new text, but the color doesn't
change as a result.
Thanks.

"T-®ex" wrote:


Hi Kenny! Try this...

Put the ff code for your worksheet (right-click on your sheet's tab,
then click, 'View Code...'):


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim TargetValue As String
TargetValue = UCase(Target.Value)

If TargetValue = "RED" Then
Target.Interior.Color = RGB(255, 0, 0)
ElseIf TargetValue = "YELLOW" Then
Target.Interior.Color = RGB(255, 255, 0)
ElseIf TargetValue = "GREEN" Then
Target.Interior.Color = RGB(0, 255, 0)
ElseIf TargetValue = "BLUE" Then
Target.Interior.Color = RGB(0, 0, 255)
ElseIf TargetValue = "BLACK" Then
Target.Font.Color = RGB(255, 255, 255)
Target.Interior.Color = RGB(0, 0, 0)
ElseIf TargetValue = "GREY" Then
Target.Interior.Color = RGB(127, 127, 127)
ElseIf TargetValue = "PURPLE" Then
'on my comp, both rgb values seem to give same results
Target.Interior.Color = RGB(160, 32, 240) 'from http://www.pitt.edu/~nisg/cis/web/cgi/rgb.html
' Target.Interior.Color = RGB(255, 0, 255)
Else
Target.Font.ColorIndex = xlColorIndexAutomatic
Target.Interior.ColorIndex = xlColorIndexNone
End If
End Sub
--------------------


With the code above, if you type in any cell any of the texts 'Red',
'Yellow', 'Green', 'Blue', 'Black', 'Grey', or 'Purple'
(case-insensitive), the color for that cell will change accordingly...

Kenny Wrote:

I would like to have VB code that will test each cell in an entire
column
(everytime the worksheet is recalculated, if that is not too
time-consuming)
and fill the cell with a different color based upon the string in the
cell.
The possible string values are Red, Yellow, Green, Blue, Black, Grey,
Purple.
The fill color would correspond to the string value.
I can't seem to get the hang of it. Any help would be appreciated.
Thanks in
advance to all the great contributors to this forum.


--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread