Macro not working when I "Call" it from another macro, but does work when I select it individually

Question

I have a formatting macro below:

Sub Colour_whole_sheet()

Dim lastRow As Long
Dim lastColumn As Long

lastRow = Range("A1").End(xlDown).Row
lastColumn = Range("A3").End(xlToRight).Column

'Colour alternate rows purple / white
For Each cell In Range(Cells(1, 1), Cells(lastRow, lastColumn))
    If cell.Row Mod 2 = 1 Then
        cell.Interior.Color = RGB(242, 230, 255)
    Else
        cell.Interior.Color = RGB(255, 255, 255)
    End If
Next cell

End Sub

It doesn't run when I call it from another macro, which is just:

Sub Run_macros()

[A bunch of other subs]
Call Colour_whole_sheet
[A bunch of other subs]

End Sub

It doesn't come up with an error - it just doesn't do anything. But when I select it specifically on its own, from View > Macros > View Macros > Run, it works fine.

Do you know why this might be?

EDIT:

Sub Colour_whole_sheet()

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Calendar")

Dim lastRow As Long
Dim lastColumn As Long

lastRow = ws.Range("A1").End(xlDown).Row
lastColumn = ws.Range("A3").End(xlToRight).Column

'Colour alternate rows purple / white
For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))
    If cell.Row Mod 2 = 1 Then
        cell.Interior.Color = RGB(242, 230, 255)
    Else
        cell.Interior.Color = RGB(255, 255, 255)
    End If
Next cell

End Sub

Answer 1

Here Range("A1") is not specified in which worksheet this range is. Always specify a worksheet for all your Range(), Cells(), Rows() and Columns() objects.

Otherwise it is very likely that your code runs on the wrong worksheet. Note that this is applicable to all your macros (not just this one). Check if you have specified a worksheet everywhere, or your code might randomly work or fail.

Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet1") 'your sheet name here

Then adjust the following lines:

lastRow = ws.Range("A1").End(xlDown).Row
lastColumn = ws.Range("A3").End(xlToRight).Column

For Each cell In ws.Range(ws.Cells(1, 1), ws.Cells(lastRow, lastColumn))

Also note that you can format an Excel table to get rows alternated colored.

---

Additional notes:

The method you used is not reliable in finding the last used row/column. Better do it the other way round. Start in the very last row and go xlUp.

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Last used row
lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column 'last used column in row 3

Also you don't need to go through all cells. Looping throug rows would do.

Dim i As Long
For i = 1 To lastRow
    If i Mod 2 = 1 Then
        ws.Rows(i).Interior.Color = RGB(242, 230, 255)
    Else
        ws.Rows(i)..Interior.Color = RGB(255, 255, 255)
    End If
Next i

or if you don't want to color the whole row but only up to the last used column

ws.Cells(i, lastColumn).Interior.Color 

---

Note that coloring each row on on its own can slow down a lot if there are many rows. Therefore I suggest to collect all even/uneven rows in a reference and color it at once.

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row 'Last used row
lastColumn = ws.Cells(3, ws.Columns.Count).End(xlToLeft).Column 'last used 

Dim EvenRows As Range
Dim OddRows As Range

Dim i As Long
For i = 1 To lastRow
    If i Mod 2 = 1 Then
        If OddRows Is Nothing Then
            Set OddRows = ws.Rows(i)
        Else
            Set OddRows = Union(OddROws, ws.Rows(i))
        End If
    Else
        If EvenRows Is Nothing Then
            Set EvenRows = ws.Rows(i)
        Else
            Set EvenRows = Union(EvenRows, ws.Rows(i))
        End If
    End If
Next i

If Not OddRows Is Nothing Then OddRows.Interior.Color = RGB(242, 230, 255)
If Not EvenRows Is Nothing Then EvenRows.Interior.Color = RGB(255, 255, 255)