Conditional Format

[Guest]

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.

Dog>Row is Red, Cat>Row is Blue , Fish>Row is Green, Bird>Row is Yellow, etc.

How do I do this?
Thanks!
Michael E

 

[Bob Phillips]

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)

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.

 

[Guest]

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]

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)

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.

 

[Guest]

Well actually I didnt get it working---that was a repost for additional info
Thanks

 

[Bob Phillips]

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)

 

[Guest]

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]

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)

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