VBA Run-time error '1004': Selection.Interior.Pattern

Question

I have the following code that should simply change the decoration of the named range based on there being a value within it. If its empty it turns red and if its not it should clear all decoration. I have looked at the other questions with similar errors and cannot seem to fix my issue. I have the following code:

    For Each section In mandatoryFields

    MsgBox (ThisWorkbook.Worksheets("Worksheet").Range(section).Value)
    If Trim(ThisWorkbook.Worksheets("Worksheet").Range(section).Value) = "" Then
        ThisWorkbook.Worksheets("Worksheet").Range(section).Select

    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

    Else
        ThisWorkbook.Worksheets("Worksheet").Range(section).Select
            With Selection.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
    End If

Next section

I have stepped through the code and the runtime error is being triggered at the

.Pattern = xlNone

or

.Pattern = xlSolid

lines. Any Suggestions?

I have also tried

Worksheets("Worksheet").Activate
        Range(section).Select
            With Selection.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

I have the following code which is triggered on a button click.

    Dim wb As Workbook
Set wb = Application.Workbooks("C New Hire")
wb.Worksheets("Worksheet").Range("nameRange").Value = "r"

A runtime error will occur on the second time i click the button on the line

wb.Worksheets("Worksheet").Range("nameRange").Value = "r"

Also possibly worth noting: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — David Zemens, Jun 24 '14 at 15:31
section and mandatoryFields are not declared. use option explicit please. Declaring variables makes things easier (for you, me and all readers here). `section` looks to be a string, but probably should be a Range. `Range(Section)` makes no sense if section isn't a string, but `for each section in ...` makes it look like a Range. My advice, dim both as range, and use `with section.interior`. don't use '.select'. Also remember, you cannot select hidden cells, or sheets... — Patrick Lepelletier, Jun 25 '14 at 15:25

Gary's Student · Answer 1 · 2014-06-24T16:46:19.230

1

The Coloring part of the code is correct as this works:

Sub luxation()
    Range("A1:A2").Clear
    Range("A1").Select
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 255
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
    Range("A2").Select
        With Selection.Interior
            .Pattern = xlNone
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
End Sub

However:

Before Selecting a Range, you must Activate the "containing" worksheet.

Otherwise, just follow David's advice.

EDIT#1:

based on your updated code, immediately after:

Range(section).Select

insert:

MsgBox Selection.Address(0,0)

This will shed some light on where the problem is happening.

edited Jun 24 '14 at 16:46

answered Jun 24 '14 at 15:46

Gary's Student

95,722
10
59
99

1

so much hate goes on about the `.Select` statement – Jun 24 '14 at 15:47
2

@mehow I agree with you.........the one "good" thing about *Select* is that it gives us interesting problems to debug! – Gary's Student Jun 24 '14 at 15:50
Is the containing worksheet not active based on ThisWorkbook.Worksheets(strNewHireSheet).Range(section).Select Before I use the Selection.Interior properties – psycho Jun 24 '14 at 16:02
Like I'm explicitly saying what workbook and what range I want to select before I do anything with the selection. – psycho Jun 24 '14 at 16:05
I, personally, don't guess ................ Before *Selecting* a range on a specific worksheet of a specific workbook, I make sure it is the *Active* sheet. Otherwise I avoid *Selecting.* – Gary's Student Jun 24 '14 at 16:09
I have updated the code in the question above as you recommended with no joy – psycho Jun 24 '14 at 16:20
The Office APIs basically mimic user input. When using them, you have to think "How would I do this with a mouse and keyboard?". – JDB Jun 24 '14 at 16:21
@JDB but the point of VBA programming is not to simply *replicate* keystrokes and mouse-clicks (the macro recorder will do this for you of course), but to interact directly with the object model. – David Zemens Jun 24 '14 at 17:36
@DavidZemens - The structure of many of the API calls is based very closely on the menus and context menus. The way you work with the API is very similar to how you'd work with the UI - for example, needing to "select" a cell before performing an operation on it. A better API would be far more object-oriented. – JDB Jun 24 '14 at 18:30
@JDB you do **NOT** need to "select" a cell before operating on it. Probably the fourth time I've linked to [this](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) today... 99% of Select/Activate could be better written using the objects directly, rather than relying on the clunky and error-prone `Select` or `Activate` methods. – David Zemens Jun 24 '14 at 18:45
So I tried again this morning running the exact same code. No changes whatsoever. And it worked. Don't understand how this can happen – psycho Jun 25 '14 at 07:51
@psycho I am glad your problem has been resolved.......you should at least consider **David Zemens'** suggestions ............... a little re-engineering will allow you to avoid problems in the future. – Gary's Student Jun 25 '14 at 10:41
I spoke to soon It runs correctly on the first run but fails on the second. I'll update the question to show the problem as it stands at the moment – psycho Jun 25 '14 at 13:50

score 0 · Accepted Answer · answered Jun 26 '14 at 12:43

The answer to this problem is that a Worksheet_Change function was triggered after my code which executed internal code that protected the sheet somewhere in the code. This meant that I coudn't carry out any procedures on any objects such as change its colour. Therefore I had to unprotect the sheet before carrying out these procedures.

Simple fix in the end but easily missed.

VBA Run-time error '1004': Selection.Interior.Pattern

2 Answers2