[FIXED] How to make a for loop to apply conditional formating?

Issue

I created a conditional formatting for the first set of columns (C:E) (see image 3).

Range("C1:E36").Select
Selection. Format Conditions. Add Type:=xlExpression, Formulal:="=$D6=""Sun"""
Selection. Format Conditions (Selection. FormatConditions. Count).SetFirstPriority
with Selection. Format Conditions (1). Interior
.Pattern = xlLightVertical
. PatternColor = 65535
.ColorIndex = xlAutomatic
.PatternTintAndShade = 0
End With

I am trying to create a for loop that it should apply to all twelve sets - each with 3 columns (see image 2). Additionally, it should run 3 times - starting at rows C6, C45,C84 - corresponding to the three year I am trying to display (see image 1). I am struggling with the for loop. And the relative abs reference on columns of $D6 in the conditional formatting and how to make that be $G6, $J6, $D84, $G84.

For o = 1 TO 3 Step 1
    For I = 1 To 12 Step 1
    Range (.Cells(6, I * 3), .Cells (36, I * 3 + 2)).Select
    Selection. Format Conditions. Add Type:=xlExpressionFormulal:="=$D6=""Sun"""
    Selection. Format Conditions (Selection. Format Conditions. Count).SetFirstPriority
    With Selection. Format Conditions (1). Interior
   .Pattern = xlLightvertical
   .PatternColor = 65535
   .ColorIndex = xlAutomatic
   .PatternTintAndShade = 0
    End With
    Next I
Next o
End Sub'

Solution

For copying formatting, I suggest .Copy and .PasteSpecial using xlPasteFormats. As for dynamically determining the ranges, since yours have a regular sizing and predictable location, its simplest to write a static For Loop to iterate the Row and Column numbers.

Sub Example()
    Dim r As Long, c As Long 
    For r = 6 To 84 Step 39
        For c = 3 To 36 Step 3
            Cells(r, "C").Resize(38, 3).Copy
            Cells(r, c).Resize(38, 3).PasteSpecial xlPasteFormats
        Next
    Next
End Sub

This code Copies formatting from "C6:E44" onto the adjacent columns. 12 Sets, each 3 columns wide (Eg "F6:H44","I6:K44"). Then it advances the row number from 6 to 45 and does it again, copying "C45:E83" onto "F45:H83" and the other 11 column sets. Then it advances from row 45 to row 84 and does this again.

In Response to your comments about applying a new/custom formatting for each range:

Sub Example()
    For r = 6 To 84 Step 39
        For c = 3 To 36 Step 3
            ApplyFormatting Cells(r, c).Resize(38, 3)
        Next
    Next
End Sub

Sub ApplyFormatting(InRange As Range)
    InRange.FormatConditions.Add Type:=xlExpression, Formula1:="=" & InRange.Cells(1, 2).Address(False, True) & "=""Sun"""
    InRange.FormatConditions(InRange.FormatConditions.Count).SetFirstPriority
    With InRange.FormatConditions(1).Interior
        .Pattern = xlLightVertical
        .PatternColor = 65535
        .ColorIndex = xlAutomatic
        .PatternTintAndShade = 0
    End With
End Sub

This procedure ApplyFormatting takes each range and uses the address as part of a new formatting formula applied to the whole range.

Answered By - Toddleson

Answer Checked By - Timothy Miller (PHPFixing Admin)