How to make a for loop to apply conditional formating?-CodePudding

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'

CodePudding user response：

You could define a Sub that CF's a passed range as you wish. Then call that as many times as you need, to set all columns

Setting the Conditional Formatting

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

Calling it for a range

Sub Demo1()
    SetCF ActiveSheet.Range("C1:E36")
End Sub

Calling it for several offset ranges

Sub Demo2()
    Dim r As Range
    Dim i As Long
    
    Set r = ActiveSheet.Range("C1:E36")
    For i = 0 To 11
        SetCF r.Offset(, i * 3)
    Next
End Sub

CodePudding user response：

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.

CodePudding user response：

It would be much simpler to:

Create conditional formatting so it will copy both down and across
Copy and paste formatting to the other locations

To do 1) Define conditional formatting separately for each cell C6, D6 and E6, using formula: =OR(D6="Sat",D6="Sun").

You can now do 2):

Select cells C6:E6 and Copy
Select a target group (say) F6:H36 and Paste Formatting
Now Copy F6:H36 and Paste Formatting to each of the other column groups