runtime error 1004

[Fred Evans]

Hello Everyone
I wrote a macro which is supposed to change the fill color of a cell- but when I run the macro
i get the error "runtime error 1004 application defined or object defined error"

what is it trying to tell me and how do i get the thing to work??

This is the macro

Sub blueme()
'
' blueme Macro
' Makes blue fill in cell
'
' Keyboard Shortcut: Ctrl+b
'
    ActiveCell.Offset(-2, -22).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent5
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

thanks

fred from south africa

 

[Gary McMaster]

Hi Fred,

If the active cell (whatever is selected before you run the code) is not in column "W" (or further to the right) your offset (-2, -22) tries to select a cell that is off the sheet. Negative row or column numbers are not allowed. It will also fail if you are in row 1 or 2. Can you better explain what you are trying to do?

Gary

 

[Fred Evans]

Gary thanks--
Why do you say column "W" ??
regards

fred

 

[Gary McMaster]

"W" is the 23rd column. The offset of -22 yields column 1 which is valid.

 

[Fred Evans]

Ok thats interesting.
Now this macro was created by the process of recording the macro in the development
tab . where would this offset have come from ??
(regarding what I was trying to do - I want alt q to change the fill of a cell to a different colour.
I rewrote the macro and it works OK now)
thanks for the feedback

 

[Gary McMaster]

where would this offset have come from ??

It's hard to say. I often look at recorded code and wonder why it adds lots of extra code. In lieu of clairvoyance, the developers were just trying to cover as many angles as they could.

Glad you got it working.

 

[Fred Evans]

Thanks Gary-
Could it be that the cursor happened to be in that cell when the macro was recorded??


I like your signature -- a nice development of it could be
"If absolutely nothing can go wrong then something will go wrong and if everything possible could go wrong
then everything possible will go wrong