Loop Not Looping too Next Sheet

PishoiNassif

New Member
Joined
Nov 28, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello,

Can you help me with this? My code isn't looping to the next worksheet. I'm very new to VBA so please bear with me as the code isn't nearly as clean as it should be. Thank you.

Sub TrendedConditionalFormatting()

' TrendedConditionalFormatting Macro
' This macro formats the variance columns in the trended tab by the parameters found in the the Input tab.
Dim wb As Workbook
Dim sh As Worksheet

On Error Resume Next

For Each wb In Application.Workbooks
For Each sh In wb.Worksheets
Range("R12:R614").Select
Range("R12").Activate
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="=Input!$E$13"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("R12:R614").Select
Range("R505").Activate
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=-Input!$E$13"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("S12:S501").Select
Range("S12:S501,S505:S614").Select
Range("S505").Activate
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:="=Input!$E$14"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("S505:S614").Select
Range("S614").Activate
Range("S505:S614,S12:S501").Select
Range("S501").Activate
Application.CutCopyMode = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
Formula1:="=-Input!$E$14"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("R12:S614").Select
Selection.Copy
Range("T12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("V12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AC12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Range("AE12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Rows("1:3").Select
Selection.EntireRow.Hidden = True
Range("A4").Select

Next
Next

On Error GoTo 0

End Sub
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
This only loops through the sheets, but doesn't actually activate/move to them:
Rich (BB code):
For Each sh In wb.Worksheets

If you select the sheet, then all your range references will run against that sheet and not your original active sheet, i.e.
Rich (BB code):
For Each sh In wb.Worksheets
    sh.Activate
 
Upvote 0
Solution
This only loops through the sheets, but doesn't actually activate/move to them:
Rich (BB code):
For Each sh In wb.Worksheets

If you select the sheet, then all your range references will run against that sheet and not your original active sheet, i.e.
Rich (BB code):
For Each sh In wb.Worksheets
    sh.Activate

Thank you so much. This worked perfectly!
 
Upvote 0
You are welcome.
Glad I was able to help!
 
Upvote 0

Forum statistics

Threads
1,223,157
Messages
6,170,419
Members
452,325
Latest member
BlahQz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top