CODEWITHSUNDEEP
vbaexcelloopsmacrosconditional-formatting
D.Sanderson
D.Sanderson

Reputation: 13

VBA - Conditional Formatting Multiple Ranges of Cells and Looping

I am creating a Master Schedule for work and am trying to display information so it is very intuitive to the user.

So... we would like to be able to Highlight Cells J4:L4 when a date is entered in Cell K4. Then, we want to loop it for Rows 4 through 2500 and Columns M:0, P:R, S:U, etc.... I have the following Macro written but it is only highlighting the first Row. Can someone point me in the right direction?

    Sub Highlight()
    '
    ' Highlight Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+R
    '
    Dim kRange As Range, k As Integer, aaaFormat As FormatCondition
        If ActiveSheet.Name <> Sheet1.Name Then Exit Sub
    For k = 4 To 2500
        Set kRange = Range("=$J4:$L4")
        If kRange.FormatConditions.Count <> 0 Then
            kRange.FormatConditions.Delete
        Else
            Set aaaFormat = kRange.FormatConditions.Add(xlExpression, xlFormula, "=$K4<>0")
            aaaFormat.Interior.Color = 15773696
        End If
    Next k
    End Sub

Upvotes: 1

Views: 4163

Answers (1)

user4039065
user4039065

Reputation:

While you are cycling through the loop, you need to adjust the range the CF rule will govern as well as the formula that determines its outcome.

'this,
Set kRange = Range("$J4:$L4")
'becomes,
Set kRange = Range("$J"& k & ":$L"& k)
'and this,
Set aaaFormat = kRange.FormatConditions.Add(xlExpression, xlFormula, "=$K4<>0")
'becomes,
Set aaaFormat = kRange.FormatConditions.Add(xlExpression, xlFormula, "=$K" & k & "<>0")

However, since you've already made the row relative and the column absolute, you can simply write the CF to the entire range.

Sub Highlight()
    ' Highlight Macro
    ' Keyboard Shortcut: Ctrl+Shift+R

    With Sheet1.Range("J4:L2500")
        .FormatConditions.Delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=$K4<>0")
            .Interior.Color = 15773696
        End With
    End With
End Sub

Upvotes: 3

Related Questions