Conditional Formatting of 4 criteria
 

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Set the cells to a default colour, then you can add the 3 CFs to get 4.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"gibsol" wrote in message
...
My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I
need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Thanks for the speedy response.

My default is, shall we say white, this is if all results exceed the set
limits and are considered to be OK, it is the
IF D11=420 and J11 <300000 format orange that is causing the problem,
therefore from what you are saying is I that I really need to be able to set
5 CF's.

"Bob Phillips" wrote:

Set the cells to a default colour, then you can add the 3 CFs to get 4.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"gibsol" wrote in message
...
My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I
need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

But the OP said that he wanted 4 different colours beyond the original
format colour,
so either use Excel 2007, which allows more than 3 CF conditions,
or use VBA, such as http://www.vbaexpress.com/kb/getarticle.php?kb_id=90
--
David Biddulph

"Bob Phillips" wrote in message
...
Set the cells to a default colour, then you can add the 3 CFs to get 4.

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"gibsol" wrote in message
...
My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I
need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay
at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

If you're using Shane's macro as a manually-run macro as he posted, you can
add a line to clear the cells colors then re-paint according to updated
values.

Sub myFormat()
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone 'added this
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

But............how are the values in D and J changing?

Are they formula-derived?

If so you may want event code.

Post back.


Gord Dibben MS Excel MVP




On Thu, 2 Oct 2008 03:39:00 -0700, gibsol
wrote:

Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Gord thanks for this.

The values are changed by Query download then Pivot table refresh.

Hope this explains well enough.

"Gord Dibben" wrote:

If you're using Shane's macro as a manually-run macro as he posted, you can
add a line to clear the cells colors then re-paint according to updated
values.

Sub myFormat()
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone 'added this
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

But............how are the values in D and J changing?

Are they formula-derived?

If so you may want event code.

Post back.


Gord Dibben MS Excel MVP




On Thu, 2 Oct 2008 03:39:00 -0700, gibsol
wrote:

Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to Excel window.


Gord

On Thu, 2 Oct 2008 23:38:00 -0700, gibsol
wrote:

Gord thanks for this.

The values are changed by Query download then Pivot table refresh.

Hope this explains well enough.

"Gord Dibben" wrote:

If you're using Shane's macro as a manually-run macro as he posted, you can
add a line to clear the cells colors then re-paint according to updated
values.

Sub myFormat()
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone 'added this
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

But............how are the values in D and J changing?

Are they formula-derived?

If so you may want event code.

Post back.


Gord Dibben MS Excel MVP




On Thu, 2 Oct 2008 03:39:00 -0700, gibsol
wrote:

Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Great works a treat will use this for ever !.

Thanks to all you guys for the help. The info looks a treat.

"Gord Dibben" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code".

Copy/paste into that sheet module.

Alt + q to return to Excel window.


Gord

On Thu, 2 Oct 2008 23:38:00 -0700, gibsol
wrote:

Gord thanks for this.

The values are changed by Query download then Pivot table refresh.

Hope this explains well enough.

"Gord Dibben" wrote:

If you're using Shane's macro as a manually-run macro as he posted, you can
add a line to clear the cells colors then re-paint according to updated
values.

Sub myFormat()
Dim cell As Range
Range("E11:J107").Cells.Interior.ColorIndex = xlNone 'added this
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

But............how are the values in D and J changing?

Are they formula-derived?

If so you may want event code.

Post back.


Gord Dibben MS Excel MVP




On Thu, 2 Oct 2008 03:39:00 -0700, gibsol
wrote:

Shane thanks it works great. I do not pretend to understand your VBA code, so
can I be cheeky and ask how would I change the code to change the colouring
when the original total increases past the criteria, at present the row stays
coloured to the original numbering no matter if it increases past the limits.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance

Conditional Formatting of 4 criteria
 

Cheers Shane works great.

"ShaneDevenshire" wrote:

Hi,

Here is the code you need for your example:

Sub myFormat()
Dim cell As Range
For Each cell In [E11:E107]
With cell
If .Offset(0, -1) = 120 And .Offset(0, 5) < 235000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 3
ElseIf .Offset(0, -1) = 220 And .Offset(0, 5) < 245000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 4
ElseIf .Offset(0, -1) = 320 And .Offset(0, 5) < 265000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 41
ElseIf .Offset(0, -1) = 420 And .Offset(0, 5) < 300000 Then
Range(cell, .Offset(0, 4)).Interior.ColorIndex = 44
End If
End With
Next cell
End Sub

Note you did not tell us what you wanted to trigger that change so this runs
manually.
You could trigger this macro with a Worksheet_Change event or a
Worksheet_Calculate event or a Worksheet_SelectionChange event or just add a
shortcut key.
--
Thanks,
Shane Devenshire


"gibsol" wrote:

My spreadsheet (2003) ranges from E11 to I107, I have 4 products that I need
to format row colour on.
The criteria would be :
IF D11=120 and J11 <235000 format the row red,
IF D11=220 and J11 <245000 format green,
IF D11=320 and J11 <265000 format blue,
this I can do, it is when I get to the 4th criteria that I am at a loss.
How do I get the 4th item to be included in the conditional format,
IF D11=420 and J11 <300000 format orange.
But if all rows are greater than the limits set then the row is to stay at
the original format colour.

Thanks in advance