loop macro through all worksheets

Question

Im trying to loop this macro, which changes the color of the row based upon year, through all of my worksheets in a current workbook and can't seem to figure out how to do so. I've tried to piece some stuff together from other questions and answers to no avail. Any help would be appreciated. Here is the code:

Sub ExpirationYeartoColors()
Dim num As Integer, lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.Select

For r = 2 To lr

Select Case Range("A" & r).Value
    Case Is = "2015"
    Range("A" & r).Interior.Color = RGB(181, 189, 0)
    Case Is = "2016"
    Range("A" & r).Interior.Color = RGB(0, 56, 101)
    Case Is = "2017"
    Range("A" & r).Interior.Color = RGB(0, 147, 178)
    Case Is = "2018"
    Range("A" & r).Interior.Color = RGB(155, 211, 221)
    Case Is = "2019"
    Range("A" & r).Interior.Color = RGB(254, 222, 199)
    Case Is = "2020"
    Range("A" & r).Interior.Color = RGB(238, 242, 210)
    Case "2020" To "2080"
    Range("A" & r).Interior.Color = RGB(238, 242, 210)
    Case Is = "Unknown"
    Range("A" & r).Interior.Color = RGB(197, 200, 203)
    Case Is = "Available"
    Range("A" & r).Interior.Color = RGB(247, 150, 91)
    Case Is = "CommonArea"
    Range("A" & r).Interior.Color = RGB(230, 230, 230)
    Case Else
    Range("A" & r).Interior.Color = RGB(255, 255, 255)
End Select
Next r

On Error GoTo ErrorHandler
   ' Insert code that might generate an error here
   Exit Sub
ErrorHandler:
   ' Insert code to handle the error here
   Resume Next

End Sub

Are the values in column A actually a 4 digit text value that looks like a number representing a year or are they dates that may be formatted to display *yyyy*? Have you considered [Conditional Formatting](https://support.office.com/en-au/article/Use-a-formula-to-apply-conditional-formatting-fed60dfa-1d3f-4e13-9ecb-f1951ff89d7f)? — , Jun 10 '15 at 18:23
`for each worksheet in thisworkbook.sheets` will loop through the sheets — Raystafarian, Jun 10 '15 at 18:25
@Raystafarian - That sounds like you are encouraging the use of `.Select` to transfer worksheet control. -1. — , Jun 10 '15 at 18:28
@Jeeped I guess it might, huh? I wasn't paying much attention, I wouldn't use `select` as I also wouldn't use cases like this. — Raystafarian, Jun 10 '15 at 18:30

nirmalraj17 · Answer 1 · 2015-06-10T21:09:51.310

I have calculated the count of number of worksheets available on the workbook and stored to a variable. Then used a for loop to loop through the entire workbook till the last worksheet.

The blocked code is the modified part on your code.

Also I see for the case 2020 - 2080, the formatting color is same.

Sub ExpirationYeartoColors()
Dim num As Integer, lr As Long, r As Long

t = ActiveWorkbook.Worksheets.Count
i = 0
For i = 1 To t
Worksheets("sheet" & i).Activate

lr = Cells(Rows.Count, "A").End(xlUp).Row

ActiveSheet.Select

For r = 2 To lr

Select Case Range("A" & r).Value
    Case Is = "2015"
    Range("A" & r).Interior.Color = RGB(181, 189, 0)
    Case Is = "2016"
    Range("A" & r).Interior.Color = RGB(0, 56, 101)
    Case Is = "2017"
    Range("A" & r).Interior.Color = RGB(0, 147, 178)
    Case Is = "2018"
    Range("A" & r).Interior.Color = RGB(155, 211, 221)
    Case Is = "2019"
    Range("A" & r).Interior.Color = RGB(254, 222, 199)
    Case Is = "2020"
    Range("A" & r).Interior.Color = RGB(238, 242, 210)
    Case "2021" To "2080"
    Range("A" & r).Interior.Color = RGB(238, 242, 210)
    Case Is = "Unknown"
    Range("A" & r).Interior.Color = RGB(197, 200, 203)
    Case Is = "Available"
    Range("A" & r).Interior.Color = RGB(247, 150, 91)
    Case Is = "CommonArea"
    Range("A" & r).Interior.Color = RGB(230, 230, 230)
    Case Else
    Range("A" & r).Interior.Color = RGB(255, 255, 255)
End Select
Next r

Next i

On Error GoTo ErrorHandler
   ' Insert code that might generate an error here
   Exit Sub
ErrorHandler:
   ' Insert code to handle the error here
   Resume Next

End Sub

This will loop through all the sheets and do the formatting. The code is tested and is working fine

Dear All, Please let me know why my solution was downvoted so that I can correct those in future. As per the question, I have created the excel with the necessary data for simulation and tested. And after successful results only I have posted my reply. — nirmalraj17, Jun 12 '15 at 01:41

score 0 · Answer 2 · answered Jun 10 '15 at 18:36

0

Here is your answer...you need a variable to count the sheets then put your loop inside another "for" to go through all the sheets.

Or you could probably use a while if you wanted..

answered Jun 10 '15 at 18:36

DCX

102
5

score 0 · Answer 3 · edited May 23 '17 at 12:14

There are a few ways to loop through the worksheets in a workbook. I prefer the worksheet index method which simply identifies the worksheet according to its position in the worksheet queue.

Sub ExpirationYeartoColors()
    Dim w As Long, lr As Long, r As Long, vVAL As Variant

    For w = 1 To Worksheets.Count
        With Worksheets(w)
            lr = .Cells(Rows.Count, "A").End(xlUp).Row
            For r = 2 To lr
                vVAL = .Range("A" & r)
                If IsNumeric(vVAL) Then
                    'treat numbers as numbers!!!
                    vVAL = Int(vVAL)   'maybe vVAL = Year(vVAL) ?
                    Select Case vVAL
                        Case 2015
                            .Range("A" & r).Interior.Color = RGB(181, 189, 0)
                        Case 2016
                            .Range("A" & r).Interior.Color = RGB(0, 56, 101)
                        Case 2017
                            .Range("A" & r).Interior.Color = RGB(0, 147, 178)
                        Case 2018
                            .Range("A" & r).Interior.Color = RGB(155, 211, 221)
                        Case 2019
                            .Range("A" & r).Interior.Color = RGB(254, 222, 199)
                        Case 2020
                            .Range("A" & r).Interior.Color = RGB(238, 242, 210)
                        Case 2021 To 2080
                            .Range("A" & r).Interior.Color = RGB(238, 242, 210)
                        Case Else
                            .Range("A" & r).Interior.Pattern = xlNone
                    End Select
                Else
                    Select Case vVAL
                        Case Is = "Unknown"
                            .Range("A" & r).Interior.Color = RGB(197, 200, 203)
                        Case Is = "Available"
                            .Range("A" & r).Interior.Color = RGB(247, 150, 91)
                        Case Is = "CommonArea"
                            .Range("A" & r).Interior.Color = RGB(230, 230, 230)
                        Case Else
                            .Range("A" & r).Interior.Pattern = xlNone
                    End Select
                End If
            Next r
        End With
    Next w


On Error GoTo ErrorHandler
   ' Insert code that might generate an error here
   Exit Sub
ErrorHandler:
   ' Insert code to handle the error here
   Resume Next

End Sub

There are a number of unanswered questions; particularly about the nature of the data. However, you should be treating numbers as numbers especially if you want to use them in something like Case "2020" To "2080". I've tried to determine the nature of the values and treated text and numbers separately. This compiles but with no sample data or answers to the comments posed I cannot guarantee its validity.

Setting the .pattern to xlNone removes the interior fill rather than painting it white.

See How to avoid using Select in Excel VBA macros for more methods on getting away from relying on select and activate to accomplish your goals.

score 0 · Answer 4 · answered Jul 26 '15 at 16:48

There is an alternative approach using conditional formatting (CF).

The advantage is that once the CF is set up, th formatting of cells will respond to any values that are changed.

You could use VBA code to add CF to all sheets by looping through all sheets in the workbook and running VBA code to add it. OR you could add it manually as follows.

I understand you might need to write VBA code for other reasons and if you do the other answers are good, but I suspect this might work for you.

Select all rows on a sheet (or as many as you need).

Ribbon>HOME>Conditional formatting
Choose: "Use a Formula to determine which cells to format"

Enter this formula  "=AND($A1=2010,$A1>0)"
(it assume your data value is in column A
 it assumes the first row you selected was row 1)

Enter the formatting you want for the whole row when year in column A=2010

Add one conditional formatting for every year.

I recommend you record a macro and just change it as needed to add CF for every year to every sheet.

Sometimes simple is best.

Harvey

loop macro through all worksheets

4 Answers4