PishoiNassif
New Member
- Joined
- Nov 28, 2023
- Messages
- 3
- Office Version
- 365
- Platform
- 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
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