I'm trying to loop all the visible sheets in a workbook, then set a Conditional Format to a range of cells on each sheet. The code below works for me, but only on the first sheet. I can't seem to get it to work on the rest of the sheets. Any suggestions would be appreciated.
Thanks!
Sub CondForm3()
Dim r As Range
Dim wksheet As Worksheet
Dim n As Long
For Each wksheet In Worksheets
If wksheet.Visible = True Then
n = n + 1
End If
Next wksheet
For i = 1 To n
Set sh = Worksheets(i)
Set r1 = sh.Range("AC11:AC37")
For Each r In r1
'r1.Select
Application.Range("AC11:AC37").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="=$AB$9"
Selection.FormatConditions(1).Font.ColorIndex = 3
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$AC$9"
Selection.FormatConditions(2).Font.ColorIndex = 3
Next r
Next
End Sub
Bookmarks