Using Conditional Formatting to Color Cells

[crap]

I am using Conditional Formatting to color rows of cells depending on
the value of a certain cell.

Specifically I am using:

If Formula is... =$C4="W" and if C4 is W the row is shaded green.
Similarly for other letter values of cells in column 4.

My problem is that Conditional Formatting seems limited to 3
conditions. I would like to be able to color based on 4 different
values. Can I expand CF to more than three conditions? Or can I
specify multiple values (i.e if C4="D" or "R" color the row red)?

Thanks,

Ian

 

[Bob Phillips]

Ian,

You have 3 choices

1. Set all the CF cells to a particular colour, and then set the 3
conditions for the other colours (default condition takes a colour)

2. Use event code, something like

Private Sub Worksheet_Change(ByVal Target As Range)


On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("C1:C50")) Is Nothing Then
With Target
Select Case UCase(.Value)
Case "00:01 - 03:00": .Interior.ColorIndex = 34
Case "03:01 - 08:00": .Interior.ColorIndex = 35
Case "08:01 - 15:00": .Interior.ColorIndex = 5
Case "15:01 - 18:00": .Interior.ColorIndex = 46
Case "18:01 - 21:00": .Interior.ColorIndex = 7
Case "21:01 - 24:00": .Interior.ColorIndex = 3
'etc.
End Select


End With
End If


ws_exit:
Application.EnableEvents = True
End Sub


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


3. Download the free add-in at
http://www.xldynamic.com/source/xld.CFPlus.Download.html


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Keith Streich]

Is there anyway option 2 could be incorporated to work with a recalc? The
event code shown only work when entering data in the specified range.

Keith

 

[JulieD]

Hi

"Or can I specify multiple values (i.e if C4="D" or "R" color the row
red)?"

yes - choose formula is and type
=OR(C4="D",C4="R")
choose Red

Cheers
JulieD

 

[Bob Phillips]

Maybe, but what would you be testing for? A range of cells that meet certain
criteria?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Keith Streich]

yes, but they will change value after I input a value in another cell and
recalc.

 

[Bob Phillips]

Okay, so assuming there are four cells, H1, H2, H3, H4 that all depend upon
A10, this is the sort of thing

Private Sub Worksheet_Calculate()
With Me
If .Range("A10").Value = "abc" Then
.Range("H1").Interior.ColorIndex = 3
.Range("H2").Interior.ColorIndex = 4
.Range("H4").Interior.ColorIndex = 5
.Range("H5").Interior.ColorIndex = 6
End If
End With
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)