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.

Hi Kenny,
See http://www.mvps.org/dmcritchie/excel/event.htm#case

For the formulas you can go through the formula on an
Worksheet_calculate event and the inside would look something like:
instead of selection you would use your range or cells. for the
entire sheet -- you would be limited to formulas.
Dim CurrCell As Range
On Error Resume Next 'in case nothing in selection
For Each CurrCell In Selection.SpecialCells(xlFormulas)
CurrCell.Formula = Trim(CurrCell.Formula)
Next
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Kenny" wrote in message ...
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.

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...hreadid=465732

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...hreadid=465732