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

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

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

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

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

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

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

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