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,
|