Using Conditional Formatting to Color Cells

  • Thread starter Thread starter crap
  • Start date Start date
C

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
 
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)
 
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
 
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)
 
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)
 
Back
Top