VBA code for conditional formatting

[Srikanth H N]

Hi,

I have a data from cell C4:Fxxxx (the last row is defined as xxxx because, each time the row gets chagned, it's not constant).
I am looking for some assistance on how to define formula for conditional formatting.

If cell value of F4 <=500000, corresponding cell (ie C4 in this situation) should be green color, similarly it should do for all cells in that column.

I have written the following code, but with no luck. Any assistance!!!

Here is my code

Sub Test()
Dim ws As Worksheet
    Dim lRow As Long
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        .Range("E4:E" & lRow).Formula = "=If(D4>=0,0,D4)"
        .Range("E4:E" & lRow).Value = .Range("E4:E" & lRow).Value
        .Range("F4:F" & lRow).Formula = "=If(E4<>0,ABS(sum(E$3:E4)),"""")"
        .Range("F4:F" & lRow).Value = .Range("F4:F" & lRow).Value
        If (Range("F4:F" & lRow).Value) <= 500000 Then
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99481185338908E-02
        .PatternTintAndShade = 0
    End With
    Else
    Range("A1").Select
    End If
    End With
End Sub

Thanks!

[protonLeah]

Why not just apply the Conditional Formatting directly to column C? You can also use the macro recorder to capture to code and add it to your macro.

[stnkynts]

You cannot apply an if statement to a range consisting of more than 1 cell. You need to incorporate a loop for that. Something like this (untested):

Sub Test()
Dim ws As Worksheet
    Dim lRow As Long
    Dim rCell As Range
    Set ws = ThisWorkbook.Sheets("Sheet1")
    With ws
        lRow = .Range("D" & .Rows.Count).End(xlUp).Row
        .Range("E4:E" & lRow).Formula = "=If(D4>=0,0,D4)"
        .Range("E4:E" & lRow).Value = .Range("E4:E" & lRow).Value
        .Range("F4:F" & lRow).Formula = "=If(E4<>0,ABS(sum(E$3:E4)),"""")"
        .Range("F4:F" & lRow).Value = .Range("F4:F" & lRow).Value
        
        For Each rCell In .Range("F4:F" & lRow)
        If rCell.Value <= 500000 Then
            With rCell.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -9.99481185338908E-02
            .PatternTintAndShade = 0
            End With
        End If
        Next rCell
        
    End With
End Sub

[Srikanth H N]

Thank you... By using the above code i am having the conditioanl formatting in column F. But i want the formatting to happen in COlumn D based on values in column F.

[stnkynts]

Use the offset feature

With rCell.Offset(0, -2).Interior 'rCell is column F.  Offset -2 columns for column D