How can I get my Macro to set a range when it moves to a new sheet

[Mr House]

Hi All,

Disclaimer - New to coding.

I have a macro which scrolls through all sheets in a workbook and does "X". However I require a range to be re-set on each sheet, I can't get this to work.
It appears that the macro is changing sheets but not moving the range of cells to do the action on, I.E. the process reoccurs on the first sheet in the workbook only

The part of the code which I can't get right is in Bold.


Any help would be awesome.

Option Explicit

Sub Recolour()
Application.ScreenUpdating = False
Dim Sheet As Worksheet
Dim Rng As Range
Dim OldColour As Variant
Dim NewColour As Variant
Dim Cell As Range

OldColour = 128
NewColour = RGB(134, 38, 51)

For Each Sheet In ThisWorkbook.Worksheets
   Set Rng = ActiveSheet.Range("A1:BZ600")
        For Each Cell In Rng.Cells
            Cell.Font.Name = "Gills Sans MT"
            If Cell.Interior.Color = OldColour Then
            Cell.Interior.Color = NewColour
            End If
        Next Cell
Next Sheet

ActiveWorkbook.Save
ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

[Hydraulics]

Change the instruction to

Set Rng = Sheet.Range("A1:BZ600")

so that you are formatting each Sheet in the collection and not only the Active one.

[Mr House]

Thank you for your reply,

I actually had that originally, same problem.

Regards
Mr House

[Mr House]

Solved this :D