How to run a method in multiple worksheets without actively selecting each one

Question

I am having trouble writing some code in VBA that will run a macro for all of the sheets in an active workbook without 'manually' selecting the sheets each time and then running the method again. What I currently have is as follows:

sub NhlStatsHighlighter()

  Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets

        With ws


            For Each cell In ActiveSheet.UsedRange.Cells

                If IsNumeric(cell) And Not IsEmpty(cell) Then
                    If cell.Value < 0 Then
                    cell.Interior.Color = rgbRed

                    ElseIf cell.Value = 0 Then
                    cell.Interior.Color = rgbGray

                    ElseIf cell.Value > 0 Then
                    cell.Interior.Color = rgbLightBlue

                    End If
                End If
            Next
        End With
    Next ws

This only works on the first (active) sheet. I am not quite sure what I am doing wrong. I have tried other approaches, but I run into the same problem.

See [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) for methods on getting away from relying on select and activate to accomplish your goals. — , May 22 '15 at 23:09
@KyleStephens Also I'm not sure if this is just an example program. But you could just use conditional highlighting instead of a macro to change the color based on its value. — t3dodson, May 22 '15 at 23:25

t3dodson · Answer 1 · 2015-05-22T23:23:47.717

Your problem is you are using ActiveSheet instead of ws. ActiveSheet is the current one that the user selects. It will not update and you shouldn't select sheets in code. If you do its a matter of when not if you will get a runtime exception.

Dim ws As Worksheet
Dim cell as Range
For Each ws In ActiveWorkbook.Worksheets
    For Each cell in ws.UsedRange.Cells
        ` More stuff here
    Next
Next

You also don't need the with block. Its not doing anything here.

score 2 · Accepted Answer · answered May 22 '15 at 23:14

2

You are constructing a With ws / End With but not taking advantage of it for the parent of UsedRange .

sub NhlStatsHighlighter()
    Dim ws As Worksheet, rng as range
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            For Each rng In .UsedRange.Cells
                If IsNumeric(rng) And Not IsEmpty(rng) Then
                    If rng.Value < 0 Then
                        rng.Interior.Color = rgbRed
                    ElseIf rng.Value = 0 Then
                        rng.Interior.Color = rgbGray
                    ElseIf rng.Value > 0 Then
                        rng.Interior.Color = rgbLightBlue
                    End If
                End If
            Next
        End With
    Next ws
end sub

Note the .UsedRange.Cells. The prefixing period (aka . or full stop) makes the parent ws which changes as you go through your outer loop.

answered May 22 '15 at 23:14

Glad you got sorted out quickly. Sorry for changing your *cell* variable to *rng* but I thought I might have to refer to `.Range` and `.Cells` in my explanation and didn't want confusion. Turns out I didn't actually have to. There is nothing wrong with using *cell* as name of a variable as long as you do not confuse it with the [Range.Cells property](https://msdn.microsoft.com/en-us/library/office/ff196273.aspx). – May 22 '15 at 23:38
Ah, I see. It's fine! Just a follow up question: if I just remove the rng variable and then change everything back to cell, it should work the same, right? – Kyle Stephens May 22 '15 at 23:59
Yes, it should be fine. – May 23 '15 at 00:02

How to run a method in multiple worksheets without actively selecting each one

2 Answers2