range color formatting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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.
 
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
 
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
 
Thanks!

Tom Ogilvy said:
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
Click to expand...
 
Back
Top