Using Conditional Formatting to Color Cells

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


--

HTH

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


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

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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.....Download.html
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
>
>

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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.....Download.html
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
>
>

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

"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >
> > "Bob Phillips" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.....Download.html
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
> > >
> > >
> >
> >
>
>

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)


"Keith Streich" <[email protected]> wrote in message
news:[email protected]...
> yes, but they will change value after I input a value in another cell and
> recalc.
>
> "Bob Phillips" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> > > "Bob Phillips" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.....Download.html
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>