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.