Trying to clear the fill of a range of cells

Question

I'm trying to clear the color pattern that I've put into a range of cells. I ran record a macro to come up with the code. However, now I keep getting an error 1004, select method of range class failed

Listed below is the code I'm using

Sheets("Outputs 2").Range("B19:M24").Select
    Application.CutCopyMode = False
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Any help is greatly appreciated. This forum has helped me tremendously over the past couple of weeks.

score 1 · Answer 1 · answered Sep 10 '15 at 21:11

1

Have you renamed your worksheet since you recorded that macro?

Ensure that the name out the worksheet is exactly as it appears in your code.

answered Sep 10 '15 at 21:11

Iain Kitching

31
4

Good point. I did not think about that, but I have seen it happen before just because Excel hiccuped. – Joshua Dannemann Sep 10 '15 at 21:14
If the sheet is renamed, one would get Subscript out Of range error ;) – Siddharth Rout Sep 10 '15 at 21:51

Joshua Dannemann · Answer 2 · 2015-09-10T21:22:12.797

1

This happens sometimes and is nothing unusual. It just means that Excel was unable to set the selection. However, you can always use range references directly like so.

Sub ClearTintAndShade()
    On Error GoTo 1
    Application.CutCopyMode = False
    With ThisWorkbook.Sheets("Outputs 2").Range("B19:M24").Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Exit Sub
1:
    MsgBox ("That sheet does not exist!")
End Sub

edited Sep 10 '15 at 21:22

answered Sep 10 '15 at 21:13

Joshua Dannemann

2,003
1
14
34

I just saw your answer. Had to delete mine :D – Siddharth Rout Sep 10 '15 at 21:52
For OP: [Further Reading](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – Siddharth Rout Sep 10 '15 at 21:53
Apologies for the delay in responding to your answer. I followed the code listed above and it worked. Thank you for helping me out. – Bob Amick Sep 11 '15 at 16:41

Trying to clear the fill of a range of cells

2 Answers2