Object Required Error 424
 

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

Object Required Error 424
 

Jason,

The problem is that you have parentheses around the t when you
call FillCells. When VBA encounters parentheses in this context,
it evaluates the expression or object. Since the Value property
is the default property of a Range object, your code is executing
as if you wrote

FillCells t.Value

Simply remove the parentheses in the call to FillCells, and all
will be right with your world.

FillCells t



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jason" wrote in message
...
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

Object Required Error 424
 

Thank you!
I guess jumping around languages will always end up in syntactical errors :)