G
Guest
I'm using the following code to identify when a cell in a specific range is
modified, and then to color some cells based on these results ( I don't use
conditional formatting because it only allows 3 conditions).
When all my case statements are directly in the Worksheet_Change event
handler, then all runs fine. As soon as I move the code to its own sub and
try passing the range in, I get an Object Required error pointing to the
FillCells (t) line. Any suggestions would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Intersect(Target, Range("A3:A50"))
If t Is Nothing Then Exit Sub
FillCells (t)
End Sub
Public Sub FillCells(Target As Range)
Select Case Target
Case "X"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 15
End With
Next c
Case "P"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 6
End With
Next c
Case "L"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 45
End With
Next c
Case "D"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 50
End With
Next c
Case Else
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = xlColorIndexNone
End With
Next c
End Select
End Sub
modified, and then to color some cells based on these results ( I don't use
conditional formatting because it only allows 3 conditions).
When all my case statements are directly in the Worksheet_Change event
handler, then all runs fine. As soon as I move the code to its own sub and
try passing the range in, I get an Object Required error pointing to the
FillCells (t) line. Any suggestions would be greatly appreciated.
Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Intersect(Target, Range("A3:A50"))
If t Is Nothing Then Exit Sub
FillCells (t)
End Sub
Public Sub FillCells(Target As Range)
Select Case Target
Case "X"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 15
End With
Next c
Case "P"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 6
End With
Next c
Case "L"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 45
End With
Next c
Case "D"
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = 50
End With
Next c
Case Else
For c = 1 To 12
With Target(1, c)
.Interior.ColorIndex = xlColorIndexNone
End With
Next c
End Select
End Sub