# Solved: VBA error "400" running an Excel macro.



## exerguy

I have created a macro that goes through my workbook and is supposed to clear the interior color from cells A1:I900 on each page. When I try to run the macro I get a pop up that just says "400" in it with a red circle that has a white "X" through it. Here is the code:

Sub Clearcolors()
Dim ws As Worksheet
Dim RngH As Range
Dim RngHD As Range
For Each ws In ThisWorkbook.Worksheets
ws.Select
Set RngH = ws.Range("A1:I" & Range("I900").End(xlUp).Row)
For Each RngHD In RngH
RngHD.Interior.ColorIndex = xlNone
Next RngHD
Next ws
End Sub

What can I do to fix this problem? Any suggestions?


----------



## OBP

The first thing you need to do is to add an Error trap to find out what the Error description is, (if it can tell you that is)
Add this as the first row of code after the "Dim RngHD As Range"
On Error GoTo Errorcatch

and at the end of the code put

exit sub

Errorcatch:
MsgBox Err.Description


----------



## exerguy

Okay. I added the error catch and the error I get is:

"Method 'Select' of object '_worksheet' failed."

I'm not too sure where to go from here. Any suggestions?


----------



## bomb #21

Suggestion 1: check for hidden sheets; you'll have a job trying to select those.

Suggestion 2: explain what the purpose of the code is, since it doesn't seem to work even with ws.Select suppressed.


----------



## exerguy

I do have one hidden sheet. Unhiding it allows the macro to work, but is there a way to do this without unhiding it?


----------



## bomb #21

Not sure why you're specifying row 900. However, can't you just simplify it?

Sub Clearcolors()
For Each Sheet In ThisWorkbook.Worksheets
x = Sheet.Range("I" & Rows.Count).End(xlUp).Row
Sheet.Range("A1:I" & x).Interior.ColorIndex = xlNone
Next Sheet
End Sub


----------



## exerguy

I was specifying row 900 as that was the last row with data in it. I guess that doesn't need to be done. Simplifying it seems to work faster. 


Thanks. I'll go with this and forget about hiding the one page.


----------



## bomb #21

It ought to work for visible *and* hidden sheets.


----------



## Rollin_Again

Just add an *IF* statement to check the sheets visible property. If the worksheet is hidden you can either skip it completely like I've done in the code below or you can modify it to unhide the sheet first, process it, and then re-hide.



Code:


Sub Clearcolors()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = True Then
ws.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).Interior.ColorIndex = xlNone
End If
Next ws
End Sub

Regards,
Rollin


----------



## bomb #21

As we all know, I know squat about this "Dim" and "Set" stuff.

Rollin, I know _you_ know loads about it.

So I see this:

Set RngH = etc.

And next there's:

For Each RngHD in etc.

But there's no Set for RngHD.

So ... umm ... that's nonsense, isn't it? 

EDIT: ah; I see you edited.


----------



## Zack Barresse

Rollin_Again said:


> Just add an *IF* statement to check the sheets visible property. If the worksheet is hidden you can either skip it completely like I've done in the code below or you can modify it to unhide the sheet first, process it, and then re-hide.
> 
> 
> 
> Code:
> 
> 
> Sub Clearcolors()
> Dim ws As Worksheet
> For Each ws In ThisWorkbook.Worksheets
> If ws.Visible = True Then
> ws.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).Interior.ColorIndex = xlNone
> End If
> Next ws
> End Sub
> 
> Regards,
> Rollin


Beautiful!


----------



## exerguy

I'm with Zach on this one Rollin - Beautiful. That works better than the one I had and its simpler. Thanks for all the input Bomb, Rollin and Zach!

One more quick question: How can I set this up to skip just one sheet?


----------



## Rollin_Again

exerguy said:


> One more quick question: How can I set this up to skip just one sheet?


Just modify the existing *IF* statement to evaluate the sheetname as well. In the example below the macro will be executed on all visible sheets in the workbook except for *Sheet1*



Code:


Sub Clearcolors()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Visible = True And ws.Name <> "Sheet1" Then
ws.Range("A1:I" & Cells(Rows.Count, "I").End(xlUp).Row).Interior.ColorIndex = xlNone
End If
Next ws
End Sub

Regards,
Rollin


----------



## exerguy

Thank you for you help. This is all starting to make a little more sense to me now.


----------

