VBA code for conditional formatting

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