range color formatting
 

I have a column with multiple values. e.g. When the value of A1 changes, the
A1 changes color and the row (A1:A30) should change accordingly; and so on...

I am using the If .Column = 1 Then Select Case and works beautiful for a
cell. But I don't know how to extend it to a range. Where do I insert the
range to match the color of that cell?
Thanks for your help.
Regards,

range color formatting
 

It would help if you post the code you use now.

"Myriam" wrote:

I have a column with multiple values. e.g. When the value of A1 changes, the
A1 changes color and the row (A1:A30) should change accordingly; and so on...

I am using the If .Column = 1 Then Select Case and works beautiful for a
cell. But I don't know how to extend it to a range. Where do I insert the
range to match the color of that cell?
Thanks for your help.
Regards,

range color formatting
 

Case 1
Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3
' or
'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3

I generally don't think of A1:A30 as a row, so I offered two options - you
choose

or if that doesn't appear to be what you want, perhaps a more definitive
example.

--
Regards,
Tom Ogilvy




"Myriam" wrote in message
...
I have a column with multiple values. e.g. When the value of A1 changes,
the
A1 changes color and the row (A1:A30) should change accordingly; and so
on...

I am using the If .Column = 1 Then Select Case and works beautiful for a
cell. But I don't know how to extend it to a range. Where do I insert the
range to match the color of that cell?
Thanks for your help.
Regards,

range color formatting
 

Thanks for your response.
I'm sorry, I just realized I gave you the wrong ranges.
The first 30 cells on row 1 would be A1 thru AD1.
I need, for example, if A1 is a Yes, to change the color of A1 thru AD1.
If A2 is No, change A2 thru AD2, etc.

The code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
Select Case .value
Case "YES": .Interior.ColorIndex = 25
.Font.ColorIndex = 2
Case "NO": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
Case "Maybe": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub



"Tom Ogilvy" wrote:

Case 1
Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3
' or
'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3

I generally don't think of A1:A30 as a row, so I offered two options - you
choose

or if that doesn't appear to be what you want, perhaps a more definitive
example.

--
Regards,
Tom Ogilvy




"Myriam" wrote in message
...
I have a column with multiple values. e.g. When the value of A1 changes,
the
A1 changes color and the row (A1:A30) should change accordingly; and so
on...

I am using the If .Column = 1 Then Select Case and works beautiful for a
cell. But I don't know how to extend it to a range. Where do I insert the
range to match the color of that cell?
Thanks for your help.
Regards,

range color formatting
 

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
Select Case .value
Case "YES": .Resize(1,30).Interior.ColorIndex = 25
.Resize(1,30).Font.ColorIndex = 2
Case "NO": .Resize(1,30).Interior.ColorIndex = 2
.Resize(1,30).Font.Color = RGB(255, 0, 0)
Case "Maybe": .Resize(1,30).Interior.ColorIndex = 2
.Resize(1,30).Font.Color = RGB(255, 0, 0)
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Myriam" wrote in message
...
Thanks for your response.
I'm sorry, I just realized I gave you the wrong ranges.
The first 30 cells on row 1 would be A1 thru AD1.
I need, for example, if A1 is a Yes, to change the color of A1 thru AD1.
If A2 is No, change A2 thru AD2, etc.

The code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
Select Case .value
Case "YES": .Interior.ColorIndex = 25
.Font.ColorIndex = 2
Case "NO": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
Case "Maybe": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub



"Tom Ogilvy" wrote:

Case 1
Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3
' or
'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3

I generally don't think of A1:A30 as a row, so I offered two options -
you
choose

or if that doesn't appear to be what you want, perhaps a more definitive
example.

--
Regards,
Tom Ogilvy




"Myriam" wrote in message
...
I have a column with multiple values. e.g. When the value of A1
changes,
the
A1 changes color and the row (A1:A30) should change accordingly; and so
on...

I am using the If .Column = 1 Then Select Case and works beautiful for
a
cell. But I don't know how to extend it to a range. Where do I insert
the
range to match the color of that cell?
Thanks for your help.
Regards,

range color formatting
 

Thanks!

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
Select Case .value
Case "YES": .Resize(1,30).Interior.ColorIndex = 25
.Resize(1,30).Font.ColorIndex = 2
Case "NO": .Resize(1,30).Interior.ColorIndex = 2
.Resize(1,30).Font.Color = RGB(255, 0, 0)
Case "Maybe": .Resize(1,30).Interior.ColorIndex = 2
.Resize(1,30).Font.Color = RGB(255, 0, 0)
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub

--
Regards,
Tom Ogilvy


"Myriam" wrote in message
...
Thanks for your response.
I'm sorry, I just realized I gave you the wrong ranges.
The first 30 cells on row 1 would be A1 thru AD1.
I need, for example, if A1 is a Yes, to change the color of A1 thru AD1.
If A2 is No, change A2 thru AD2, etc.

The code I'm using is:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .Column = 1 Then
Select Case .value
Case "YES": .Interior.ColorIndex = 25
.Font.ColorIndex = 2
Case "NO": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
Case "Maybe": .Interior.ColorIndex = 2
.Font.Color = RGB(255, 0, 0)
End Select
End If
End With
ws_exit:
Application.EnableEvents = True
End Sub



"Tom Ogilvy" wrote:

Case 1
Range("A" & Target.row).Resize(1,30).Interior.ColorIndex = 3
' or
'Range("A" & Target.row).Resize(30,1).Interior.ColorIndex = 3

I generally don't think of A1:A30 as a row, so I offered two options -
you
choose

or if that doesn't appear to be what you want, perhaps a more definitive
example.

--
Regards,
Tom Ogilvy




"Myriam" wrote in message
...
I have a column with multiple values. e.g. When the value of A1
changes,
the
A1 changes color and the row (A1:A30) should change accordingly; and so
on...

I am using the If .Column = 1 Then Select Case and works beautiful for
a
cell. But I don't know how to extend it to a range. Where do I insert
the
range to match the color of that cell?
Thanks for your help.
Regards,