How do I change the color of the entire row, if one of the columns in the row
meets 1 or more criteria? I am able to change the color of the cell, but not
other columns currently.

Thanks.

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in the row
meets 1 or more criteria? I am able to change the color of the cell, but not
other columns currently.

Thanks.

hi
another way.
select the entire row with the curser in the criteria cell.
for this example i chose row 3 with criteria cell as A3
go FormatConditional Formattingchange to Formula Is and use:
=IF($A$3=2,1,0).....pick your format.
note the use of absolute references.
this sets up a true/false condition where 1 = true and 0 = false.
you could also write the formula as..
=IF($A$3=2,"True","False")
If the condition is true in the criteria cell, your formating will be
applied to the entire row.

regards
FSt1
"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in the row
meets 1 or more criteria? I am able to change the color of the cell, but not
other columns currently.

Thanks.

There seems to be a limit of three conditions. Is there anyway around if I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in the row
meets 1 or more criteria? I am able to change the color of the cell, but not
other columns currently.

Thanks.

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Thanks. This works fine. It didn't work. I just didn't understand the
values. Is there a list somewhere as to the color and associated index?

"Gord Dibben" wrote:

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Look up Colorindex property in VBA help.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
Thanks. This works fine. It didn't work. I just didn't understand the
values. Is there a list somewhere as to the color and associated index?

"Gord Dibben" wrote:

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit
and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on
the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL
wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub".
Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole
line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my
addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway
around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange
to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the
columns in
the row
meets 1 or more criteria? I am able to change the color of the
cell,
but not
other columns currently.

Thanks.

Run this macro to get a list of colors and indexes on a new sheet.

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub

Gord

On Wed, 2 Jan 2008 05:58:00 -0800, PAL wrote:

Thanks. This works fine. It didn't work. I just didn't understand the
values. Is there a list somewhere as to the color and associated index?

"Gord Dibben" wrote:

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Gord,

Method one is working great. I have made the following observation. If in
"H1" I reference a cell in another worksheet (or type in a value) and drag it
down to H10, the color is ignored for a new case. Any suggustions.

"Gord Dibben" wrote:

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.

Copying by dragging does not trigger a "change event" with method one code.

Also referencing a cell in another sheet would need a Sheet_Calculate event, not
a change_event.

The second set of code I posted should allow drag/copying down from H1 to H10
and get the colors. Try the revised code in this post.

On Sheet2 you have 1 to 6 repeated from A1:A30

In Sheet1 H1 enter =Sheet2!A1

Drag/copy down to H30 to see color banding.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("H1:H30"))
If vRngInput Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = 1: Num = 10 'green
Case Is = 2: Num = 1 'black
Case Is = 3: Num = 5 'blue
Case Is = 4: Num = 7 'magenta
Case Is = 5: Num = 46 'orange
Case Is = 6: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
endit:
Application.EnableEvents = True
End Sub

Note that a change in Sheet2 values will not trigger a color change in Sheet1.

For that you would need Worksheet_Calculate code


Gord

On Thu, 3 Jan 2008 07:16:02 -0800, PAL wrote:

Gord,

Method one is working great. I have made the following observation. If in
"H1" I reference a cell in another worksheet (or type in a value) and drag it
down to H10, the color is ignored for a new case. Any suggustions.

"Gord Dibben" wrote:

Did you place the code into the sheet module as Bob instructs?

What are the values you want to use?

Bob's code uses the numbers 1, 2, 3, or 4 You can change these to suit and
add more Cases as you need.

To have the entire row colored you have to modify the code.

Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .EntireRow.Interior.ColorIndex = 3 'red
Case 2: .EntireRow.Interior.ColorIndex = 6 'yellow
Case 3: .EntireRow.Interior.ColorIndex = 5 'blue
Case 4: .EntireRow.Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Here is an alternate set of code so's you get an idea of variations on the same
thing.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Range
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is 20: Num = 3 'red
End Select
'Apply the color
rng.EntireRow.Interior.ColorIndex = Num
Next rng
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Jan 2008 16:27:07 -0800, PAL wrote:

Hi there,

Well I pasted it in from the first, " '----" on down to "End sub". Didn't
work.
Do I have to run or execute the script or once saved it should work? I
assume it is H1:H10 where it will do the coloring? Will it do the whole line
and how will it know which response to give a certain color.

Bear with me, obviously VB is new to me.

Thanks.

"Bob Phillips" wrote:

Then you need VBA. Here is an example


'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H10" '<=== change to suit

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
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


(there's no email, no snail mail, but somewhere should be gmail in my addy)



"PAL" wrote in message
...
There seems to be a limit of three conditions. Is there anyway around if
I
have 6 conditions?

"RichardSchollar" wrote:

Hi

Select the entire row and go FormatConditional Formattingchange to
Formula Is and use:

=COUNTIF(1:1,"YourCriteria")0

this assumes that your row is row 1 - adjust to suit.

Hope this helps!

Richard


On Dec 29, 1:58 pm, PAL wrote:
How do I change the color of the entire row, if one of the columns in
the row
meets 1 or more criteria? I am able to change the color of the cell,
but not
other columns currently.

Thanks.