Conditional Formatting of 4 criteria

G

gibsol

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
 
B

Bob Phillips

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)
 
G

gibsol

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.
 
S

ShaneDevenshire

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.
 
G

gibsol

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 said:
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 said:
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
Click to expand...
Click to expand...
 
G

Gord Dibben

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




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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

gibsol

Gord thanks for this.

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

Hope this explains well enough.

Gord Dibben said:
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




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 said:
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


:

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
Click to expand...
Click to expand...
Click to expand...
 
G

Gord Dibben

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

Gord thanks for this.

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

Hope this explains well enough.

Gord Dibben said:
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




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.

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
G

gibsol

Great works a treat will use this for ever !.

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

Gord Dibben said:
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

Gord thanks for this.

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

Hope this explains well enough.

Gord Dibben said:
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




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.

:

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


:

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
Click to expand...
Click to expand...
Click to expand...
 
G

gibsol

Cheers Shane works great.

ShaneDevenshire said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Complicated Formula Problem 1
Conditional Formatting Question 2
Formatting one cell based on the contents of another 4
Conditional Formatting 1
Conditional formatting / blank cells 5
Excel Conditional Formatting 1
Conditional formatting in range 1
Conditional Formatting of a Row 2

Top