VB for conditional formatting more than 3 conditions

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" <[email protected]> wrote in message news:[email protected]...
> 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...'):

Please Login or Register  to view this content.

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...

Originally Posted by Kenny

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.

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
>
>