Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Reposted and condensed from post in Worksheet functions.....
A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
As you want more than 3 conditions you need VBA
Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row.
ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Typical boss eh? Never know what they want until you give them something,
then they know what they don't want. Glad it helped. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row. ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Well actually I didnt get it working---that was a repost for additional info
Thanks "Bob Phillips" wrote: Typical boss eh? Never know what they want until you give them something, then they know what they don't want. Glad it helped. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row. ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Oh, good job I posted back and didn't assume eh<g? I assume by row Z you
mean column Z? And surely if the value is dog, cat, etc, it can't be zero? As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("Z1:Z6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 34 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. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Well actually I didnt get it working---that was a repost for additional info Thanks "Bob Phillips" wrote: Typical boss eh? Never know what they want until you give them something, then they know what they don't want. Glad it helped. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row. ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Not zero value in Col Z but the value in the row that corresponds with, so if Z1 is dog then any cell to the left of Z1 that is 0 would be highlighted
Thanks! "Bob Phillips" wrote: Oh, good job I posted back and didn't assume eh<g? I assume by row Z you mean column Z? And surely if the value is dog, cat, etc, it can't be zero? As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("Z1:Z6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 34 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. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Well actually I didnt get it working---that was a repost for additional info Thanks "Bob Phillips" wrote: Typical boss eh? Never know what they want until you give them something, then they know what they don't want. Glad it helped. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row. ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Format
Okay, version 99.421
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim sFormula As String On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("Z1:Z6")) Is Nothing Then With Target Set rng = .Offset(0, -24).Resize(1, 24) sFormula = "=Z" & .Row & "0" Select Case LCase(.Value) Case "dog": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 3 End With Case "cat": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 5 End With Case "fish": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 10 End With Case "bird": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 19 End With Case "horse": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 20 End With Case "snake": With rng .FormatConditions.Delete .FormatConditions.Add Type:=xlExpression, Formula1:=sFormula .FormatConditions(1).Interior.ColorIndex = 34 End With End Select End With End If ws_exit: Application.EnableEvents = True End Sub -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Not zero value in Col Z but the value in the row that corresponds with, so if Z1 is dog then any cell to the left of Z1 that is 0 would be highlighted Thanks! "Bob Phillips" wrote: Oh, good job I posted back and didn't assume eh<g? I assume by row Z you mean column Z? And surely if the value is dog, cat, etc, it can't be zero? As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("Z1:Z6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 24).Interior.ColorIndex = 34 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. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Well actually I didnt get it working---that was a repost for additional info Thanks "Bob Phillips" wrote: Typical boss eh? Never know what they want until you give them something, then they know what they don't want. Glad it helped. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Thanks for the response... I had to make some slight changes for the boss------THe criteria is now in row Z, and additionally only if the cell value is greater than 0 should it be colored, ColumnB to Column Y of the row. ME "Bob Phillips" wrote: As you want more than 3 conditions you need VBA Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range("A1:A6")) Is Nothing Then With Target Select Case LCase(.Value) Case "dog": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 3 Case "cat": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 5 Case "fish": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 10 Case "bird": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 19 Case "horse": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 20 Case "snake": .Offset(0, 1).Resize(1, 10).Interior.ColorIndex = 34 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. Haven't checked the colours -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "ME" wrote in message ... Reposted and condensed from post in Worksheet functions..... A1:A6 can equal dog,cat,fish,bird,horse,snake. I want to conditionally format B1:K6 based on the A1:A6 value. I want a different color for the row dependent on the entry in Column A. DogRow is Red, CatRow is Blue , FishRow is Green, BirdRow is Yellow, etc. How do I do this? Thanks! Michael E |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
New Conditional Format Overriding Previous Conditional Format | Excel Discussion (Misc queries) | |||
How to create a conditional format that changes the number format | Excel Discussion (Misc queries) | |||
Conditional Format - Format Transfer To Chart | Excel Discussion (Misc queries) | |||
copy conditional format to regular format | Setting up and Configuration of Excel | |||
Keep format after paste from other worksheets - conditional format or EnableControl solution doesn't work | Excel Programming |