FryGirl
Well-known Member
- Joined
- Nov 11, 2008
- Messages
- 1,364
- Office Version
- 365
- 2016
- Platform
- Windows
I've been able to build one loop into this macro to switch interior.colorindex, but I would also like to loop the formula part.
I tried
but that does not work
I tried
VBA Code:
Formula1:=Fm & i
but that does not work
VBA Code:
Sub ApplyCF()
Dim wsSrc As Worksheet: Set wsSrc = Sheet5
Dim LastCol As Long: LastCol = wsSrc.Cells(5, Columns.Count).End(xlToLeft).Column
Dim LastRow As Long: LastRow = wsSrc.Range("C" & Rows.Count).End(xlUp).Row
Dim myNum As Long
Dim myColor As Variant
Const Fm1 As String = "=AND(N($D6),I$5>=$D6,I$5<=$H6)"
Const Fm2 As String = "=WEEKDAY(I$4,2)>5"
Const Fm3 As String = "=MATCH(I$4,holidays,0)"
Const Fm4 As String = "=ISODD($A6)"
Const Fm5 As String = "=ISEVEN($A6)"
Dim i As Long: i = 1
Application.ScreenUpdating = False
With wsSrc.Cells
With .FormatConditions
.Delete
End With
End With
wsSrc.Range("I6").Select
myColor = Array(32, 20)
For myNum = LBound(myColor) To UBound(myColor)
With wsSrc.Range(Cells(6, 9), Cells(LastRow, LastCol))
With .FormatConditions
.Add(Type:=xlExpression, Formula1:=Fm & i).Interior.ColorIndex = myColor(myNum)
End With
End With
i = i + 1
Next myNum
Application.ScreenUpdating = True
End Sub