ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Run-time error '424': Object required (https://www.excelbanter.com/excel-discussion-misc-queries/453098-run-time-error-424-object-required.html)

MrBadExcel

Run-time error '424': Object required
 
The code below allows me highlight one row at a time. I can scroll up and down a sheet using the arrow keys.
Sometimes need to highlight two or more rows by holding shift and clicking multiple rows. The code is only allowing me to highlight one row at a time however I can select multiple rows.

When I try to preform a delete command on multiple rows I have selected I am receiving an this error code after the delete is preformed and I then select another row to highlight:
Run-time error '424': Object required

When I click DEBUG it highlights this row in the code:
For Each Dn In rng


Option Explicit
Dim rng As Range, ray
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Dn As Range, c As Long, col As Long
col = 100
If Not rng Is Nothing Then
c = 0
For Each Dn In rng
c = c + 1
Dn.Interior.ColorIndex = ray(c, 1)
Next Dn
End If
c = 0
Set rng = Cells(Target.Row, 1).Resize(, col)
ReDim ray(1 To rng.Count, 1 To 2)
For Each Dn In rng
c = c + 1
ray(c, 1) = Dn.Interior.ColorIndex
Next Dn
Cells(Target.Row, 1).Resize(, col).Interior.ColorIndex = 6
End Sub

GS[_6_]

Run-time error '424': Object required
 
When I click DEBUG it highlights this row in the code:
For Each Dn In rng


In the case of the 1st occurance of this line, you have not 'Set' the
variable "rng" to ref a range!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

MrBadExcel

I don't know Garry. Where would I add the range? This isn't my code. I am just using it to help me navigate my excel page. I am clueless. Any help in elaborating would be greatly appreciated.

GS[_6_]

Run-time error '424': Object required
 
I don't know Garry. Where would I add the range? This isn't my code.
I am just using it to help me navigate my excel page. I am clueless.
Any help in elaborating would be greatly appreciated.


For starters.., your code is in a sheet event and so it runs every time
you select a new cell *whether you want it to or not*! Delete all the
code and put the following in a standard module...


Option Explicit


Sub HiLiteRows() 'Ctrl+Shift+H
' Shades entire rows of a selection of cells;
' Selection can be multiple areas.

Dim lColor&, vRow

lColor = 6 'Yellow
For Each vRow In Selection.Rows
vRow.EntireRow.Interior.ColorIndex = lColor
Next 'vRow

End Sub 'HiLiteRows

...and assign a keyboard shortcut to it so you can fire it only when you
choose.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

MrBadExcel

Thanks for your help Garry!

GS[_6_]

Run-time error '424': Object required
 
Thanks for your help Garry!

You're welcome! Was my solution useful?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Tim Paine

How to fix run time error 424 in Excel
 
The “Runtime Error 424 (Object Required)” error is caused by Excel being unable to access to an “object” referenced in your VBA code:
“Objects” are anything from a variable, file, worksheet, class or module.
The error is caused by a function being called on a reference you’ve either misspelled, or have not declared.
The solution is to ensure you have all the appropriate references declared within your code. The simplest ways to fix the problem is as follows:
Locate the offending line of code (if not highlighted, use debug mode)
Identify whether you’ve referenced any objects which aren’t declared
Look for any of the functions which may be causing an error and identify they are called correctly (with the correct syntax)
Remove as much code as possible in order to get the application working again, and then add back the lines one-by-one (this isolates the error and allows yo to fix any of the issues it may have)

I hope this is helpful!
Tim Paine


All times are GMT +1. The time now is 09:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com