LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming,comp.apps.spreadsheets
external usenet poster
 
Posts: 6
Default Run-time error '424': Object required

I have extracted from a larger program the following
code to color the selected cells green and their
dependents red:

Sub ColorDeps1()
Dim C As Range
Dim X As Range
For Each C In Selection
C.Interior.ColorIndex = 4 'green
If HasDependents(C) Then
For Each X In C.Dependents
X.Interior.ColorIndex = 3 'red
Next X
End If
Next C
End Sub

This code works properly. However, a slight change
causes the code to fail:

Sub ColorDeps2()
Dim C As Range
For Each C In Selection
C.Interior.ColorIndex = 4 'green
If HasDependents(C) Then
ReColorDeps (C)
End If
Next C
End Sub

Sub ReColorDeps(C As Range)
Dim X As Range
For Each X In C.Dependents
X.Interior.ColorIndex = 3 'red
Next X
End Sub

The code fails with the line ReColorDeps(C) in
the ColorDeps2 subroutine highlighted in yellow.
The only difference is that the loop over the
dependents has been cut out of the main subroutine
and placed into a separate subroutine of its own.

Unfortunately, in the larger program from which
this is a part I don't have the option of including
the loop over the dependents in the main body of
the code; it must be in a subroutine as above.

I don't understand what is wrong with ColorDeps2.
Can anyone explain the error message and tell me
how to fix my code?

By the way, my implementation of HasDependents
works but looks ugly. Does anyone have suggestions
of how to make it better?

Function HasDependents(C As Range)
On Error GoTo NoDependents
If C.Dependents.Count 0 Then
HasDependents = True
Exit Function
End If
NoDependents:
HasDependents = False
End Function

Thanks to all for your help.

Phil
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Runtime error '424': Object Required loren.pottinger Excel Discussion (Misc queries) 1 August 28th 06 09:56 PM
"Microsoft Visual Basic runtime error '424' object required". SharonG. Excel Worksheet Functions 0 July 5th 06 01:36 AM
Syntax Error Runtime Error '424' Object Required sjenks183 Excel Programming 1 January 23rd 04 09:25 AM
Dget in VBA gets Object required run-time error? John MacGregor Excel Programming 1 December 17th 03 03:44 AM
error 424 - Object Required blb Excel Programming 0 October 1st 03 05:32 PM


All times are GMT +1. The time now is 03:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"