| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA | Excel Discussion (Misc queries) | |||
| CONDITIONAL FORMATTING FOR MORE THAN 3 CRITERIA? | Excel Discussion (Misc queries) | |||
| need help with conditional formatting 3 criteria | Excel Worksheet Functions | |||
| Conditional Formatting with more than 3 Criteria | Excel Discussion (Misc queries) | |||
| Can I set more than three criteria for conditional formatting? | Excel Worksheet Functions | |||
Linear Mode
