Conditional Formatting

[Rakesh Rampiar]

Currently this fucntion only permits up to a maximum of three conditions. I
find this a bit limiting. Will Microsoft consider amending this to five or
more?

 

[Carim]

Using event macros, it gets unlimited ...

Carim

 

[Rithy Chhan]

Hello

Can you show that macro?
I also interest with this matter, because I have the same issue!

Rithy

 

[Dave Peterson]

The conditional formatting limit in the new version of excel that's scheduled to
be released next year will be limited by your pc's memory.

But until then you could use a worksheet event to look for changes.

If the change is caused by the user typing something, you could use the
worksheet_change event:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRngToCheck As Range
Dim myColorIndex As Long

Set myRngToCheck = Me.Range("a1,b9,c13:c15")

Select Case Target.Value
Case Is = 1: myColorIndex = 3
Case Is = 3: myColorIndex = 8
Case Is = 5: myColorIndex = 10
Case Else
myColorIndex = xlNone
End Select
Target.Interior.ColorIndex = myColorIndex

End Sub

You can change the range to what you want and add as many rules as as much
formatting as you want.

You can read more about events at:
Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[LRodgers]

Could this be used in the following example:

Cell A2 needs to be shaded "yellow" if A1 says "yes". Otherwise, A2 should
be shaded gray.

 

[Dave Peterson]

Maybe--it depends on how A1 changes.

But in cases like this, using Format|Conditional Formatting would be easier.

But if you want, this changes the fill color when A1 changes (by typing):

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub

If LCase(Target.Value) = "yes" Then
Target.Offset(1, 0).Interior.ColorIndex = 6
Else
Target.Offset(1, 0).Interior.ColorIndex = 15
End If

End Sub

Colors are specific to workbooks. 6 was a dark yellow and 15 was a grey for my
workbook. You may want to record a macro when you change a test cell's color.

 

[Rakesh Rampiar]

Hi Dave

Thanks for the response. I am finding difficult to run this macro
effectively. I copied it to PERSONAL.XLS and named it macro1. Typed the
numbers as you suggested in the cells that you suggested and nothing happens?

 

[Rakesh Rampiar]

Hi Dave

Thanks for the response. I've not used macros before. All I want to do is
this:

If cell is 1 colour Blue
If cell is between 1.1 and 2 colour green
If cell is between 2.1 and 3 colour Yellow
If cell is between 3.1 and 4 colour Orange
If cell is between 4.1 and 5 colour Red

Would really appreciate your help on this.

Regards

Rakesh

 

[JE McGimpsey]

If you're talking about font color, you can do it without macros:

http://www.mcgimpsey.com/excel/conditional6.html

If not, see

http://www.mvps.org/dmcritchie/excel/event.htm#calculate

for a Worksheet_Calculate() event macro that can handle multiple colors.

 

[Dave Peterson]

This kind of code doesn't go in the personal.xls workbook.

Right click on the worksheet tab that should have this behavior and select view
code. Paste the code into that window.

Make sure you review those links, too.

 

[Rakesh Rampiar]

I am talking about cell colour.

If you're talking about font color, you can do it without macros:

http://www.mcgimpsey.com/excel/conditional6.html

If not, see

http://www.mvps.org/dmcritchie/excel/event.htm#calculate

for a Worksheet_Calculate() event macro that can handle multiple colors.

 

[Rakesh Rampiar]

Dear Dave

I managed to run the worksheet macro successfully. When I change the number
on a worksheet with the macro it does automattically change the colour,
however if that same worksheet has a link on from another worksheet in the
same book and when that worksheet has been changed, the worksheet with the
macro does not automatically change colour. I would have to press F2 on that
cell for it to change colour. Is there any way around this.

Regards

Rakesh

 

[Dave Peterson]

Are you using the worksheet_calculate event (like J.E. McGimpsey suggested)?

If you are, make sure you have calculation set to automatic
(tools|Options|calculation tab).

 

[JE McGimpsey]

Then you can use my second suggestion...

I am talking about cell colour.

 

[Rakesh Rampiar]

Hi Dave

Yes (eventually), I am am using the worksheet_calculate event and checked
that the calculation tab is on automatic (default), still no joy. I went
into that website that McGimpsey suggested but because of my lack knowledge
in VBA, could not find what I am looking for.

Currently I am using this code: (as suggested by yourself)

Private Sub Worksheet_Calculate()
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRngToCheck As Range
Dim myColorIndex As Long
Set myRngToCheck = Me.Range("$a$1")
Select Case Target.Value
Case Is = 0: myColorIndex = 0 'White
Case Is = 1, Is < 1: myColorIndex = 22 'Blue
Case Is = 2, Is < 2: myColorIndex = 9 'Green
Case Is = 3, Is < 3: myColorIndex = 6 'Yellow
Case Is = 4, Is < 4: myColorIndex = 12 'Orange
Case Is = 5, Is < 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Target.Interior.ColorIndex = myColorIndex

End Sub

Now the code works perfect when I type the actual figures on any cell but
when there's a formula in a cell the colour of the cell does not change
automatically, E.g.

I type in 3 in cell A1 and 4 in cell B1, etc, cell C1 has a formula:
sum(a1:b1)/2.
When I typed 3 the cell colour changes and likewise when 4is typed, however
cell C1 only changes if I press F2 on the cell and enter it.

Please help.

Regards

Rakesh

 

[Dave Peterson]

You are using the Worksheet_change event. Notice that there is no code in the
worksheet_calculate procedure.

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myColorIndex As Long
Set myCell = Me.Range("$a$1")
Select Case myCell.Value
Case Is = 0: myColorIndex = 0 'White
Case Is <= 1: myColorIndex = 22 'Blue
Case Is <= 2: myColorIndex = 9 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 12 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex
End Sub

 

[Rakesh Rampiar]

Hi Dave

Thanks Dave. Below is how I posted it on excel, however, it now does not
want to run at all. It gives me an error '13' Type Mismatch. Sorry to
bother you.

Regards

Rakesh

Option Explicit

Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim myColorIndex As Long

Set myCell = Me.Range("$a$1")

Select Case myCell.Value
Case Is = 0: myColorIndex = 2 'White
Case Is = 1, Is < 1: myColorIndex = 5 'Blue
Case Is = 2, Is < 2: myColorIndex = 4 'Green
Case Is = 3, Is < 3: myColorIndex = 6 'Yellow
Case Is = 4, Is < 4: myColorIndex = 46 'Orange
Case Is = 5, Is < 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex

End Sub

 

[Dave Peterson]

What's in A1?

What version of excel are you using?

Maybe...

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myColorIndex As Long
Set myCell = Me.Range("$a$1")
If IsNumeric(myCell.Value) Then
Select Case myCell.Value
Case Is = 0: myColorIndex = 0 'White
Case Is <= 1: myColorIndex = 5 'Blue
Case Is <= 2: myColorIndex = 4 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 46 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Else
myColorIndex = xlNone
End If
myCell.Interior.ColorIndex = myColorIndex
End Sub

This didn't cause any trouble, but...

this line:
Case Is = 2, Is < 2: myColorIndex = 4 'Green
is the same as:
Case Is <= 2: myColorIndex = 4 'Green

I find the second one easier to read.

Hi Dave

Thanks Dave. Below is how I posted it on excel, however, it now does not
want to run at all. It gives me an error '13' Type Mismatch. Sorry to
bother you.

Regards

Rakesh

Option Explicit

Private Sub Worksheet_Calculate()

Dim myCell As Range
Dim myColorIndex As Long

Set myCell = Me.Range("$a$1")

Select Case myCell.Value
Case Is = 0: myColorIndex = 2 'White
Case Is = 1, Is < 1: myColorIndex = 5 'Blue
Case Is = 2, Is < 2: myColorIndex = 4 'Green
Case Is = 3, Is < 3: myColorIndex = 6 'Yellow
Case Is = 4, Is < 4: myColorIndex = 46 'Orange
Case Is = 5, Is < 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
myCell.Interior.ColorIndex = myColorIndex

End Sub

 

[Rakesh Rampiar]

Hi Dave

I've got Windows 2000 Professional loaded on my system, I assume I have
Excel97.

I changed the range to start from D2 instead of A1. I have 2 work sheets.
One is where I input the data and the second one is the same as first but is
used to copy all the inputs made in sheet1, specifically from columns D to H.
On sheet 2 column G is the average of D to H (as copied from sheet1). (People
get distracted by seeing the colours change on the actual sheet). I am now
encountering 2 problems:

1. How do I set my range more effectively, below is what I have to do in
order for each of those cells to be activated. I just put a few in this code
in order to illustrate my point.

2. It seems that whatever colour is on cell d2, then the rest of the range
is the same colour, irrespective of what the data in the other cells are.

Thank you so much for all your help.

Regards

Rakesh

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myColorIndex As Long
Set myCell = Me.Range
"$d$2,$d$3,$d$4,$d$5,$e$2,$e$3,$e$4,$e$5,$f$2,$f$3,$f$4,$f$5,$g$2,$g$3,$g$4,$g$5,$h$2,$h$3,$h$4,$h$5")
If IsNumeric(myCell.Value) Then
Select Case myCell.Value
Case Is = 0: myColorIndex = 0 'White
Case Is <= 1: myColorIndex = 5 'Blue
Case Is <= 2: myColorIndex = 4 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 46 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Else
myColorIndex = xlNone
End If
myCell.Interior.ColorIndex = myColorIndex
End Sub

 

[Dave Peterson]

Maybe something like:

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myRng As Range
Dim myColorIndex As Long
Set myRng = Me.Range("d2:h5")

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
Select Case myCell.Value
Case Is = 0: myColorIndex = 0 'White
Case Is <= 1: myColorIndex = 5 'Blue
Case Is <= 2: myColorIndex = 4 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 46 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Else
myColorIndex = xlNone
End If
myCell.Interior.ColorIndex = myColorIndex
Next myCell
End Sub

Depending on how your range is configured, you could build a bunch of addresses:

Set myRng = Me.Range("d2:d5,f3:f88,c239,g2:h45")

Hi Dave

I've got Windows 2000 Professional loaded on my system, I assume I have
Excel97.

I changed the range to start from D2 instead of A1. I have 2 work sheets.
One is where I input the data and the second one is the same as first but is
used to copy all the inputs made in sheet1, specifically from columns D to H.
On sheet 2 column G is the average of D to H (as copied from sheet1). (People
get distracted by seeing the colours change on the actual sheet). I am now
encountering 2 problems:

1. How do I set my range more effectively, below is what I have to do in
order for each of those cells to be activated. I just put a few in this code
in order to illustrate my point.

2. It seems that whatever colour is on cell d2, then the rest of the range
is the same colour, irrespective of what the data in the other cells are.

Thank you so much for all your help.

Regards

Rakesh

Option Explicit
Private Sub Worksheet_Calculate()
Dim myCell As Range
Dim myColorIndex As Long
Set myCell = Me.Range
"$d$2,$d$3,$d$4,$d$5,$e$2,$e$3,$e$4,$e$5,$f$2,$f$3,$f$4,$f$5,$g$2,$g$3,$g$4,$g$5,$h$2,$h$3,$h$4,$h$5")
If IsNumeric(myCell.Value) Then
Select Case myCell.Value
Case Is = 0: myColorIndex = 0 'White
Case Is <= 1: myColorIndex = 5 'Blue
Case Is <= 2: myColorIndex = 4 'Green
Case Is <= 3: myColorIndex = 6 'Yellow
Case Is <= 4: myColorIndex = 46 'Orange
Case Is <= 5: myColorIndex = 3 'Red
Case Else
myColorIndex = xlNone
End Select
Else
myColorIndex = xlNone
End If
myCell.Interior.ColorIndex = myColorIndex
End Sub