Conditional formatting VBA

[gripper]

I have a sheet with a bunch of data and I want to apply conditional formatting to Column "AK"

I want to compare the value on each row from column "D" to the same row in Column "AK" If the value in "AK" is greater then "D" then conditionally format

Below is my code. It runs until it hits a cell that should be formatted and runtime error "449"

I cannot figure out the issue. It seems like perhaps I am missing a perimeter but cannot figure it out.

My error occurs at the line that start "With ws.Cells......"

Thank you in advance for the assistance.

Sub ConditionalFormat()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ActiveSheet

    ' Find the last row with data in column D
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row

    ' Loop through all rows
    For i = 2 To lastRow
        ' Check if the value in column AK is greater than the value in column G
        If ws.Cells(i, "AK").Value > ws.Cells(i, "G").Value Then
            ' If the value in column AK is greater, apply conditional formatting
            With ws.Cells(i, "AK").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater)
                .Formula1 = "=AK" & i
                .Interior.Color = vbYellow
            End With
        End If
    Next i
End Sub

 

[bebo021999]

Not test but it seems your are trying to compare AK to itself?

With ws.Cells(i, "AK").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater)
                .Formula1 = "=AK" & i

should be?

With ws.Cells(i, "AK").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater)
                .Formula1 = "=G" & i

 

[gripper]

I tried the code change but it still breaks when the first row is true as far as should be conditionally formatting.

Thank you

 

[gripper]

I figured it out. Should read

With Cells(i, "AK").FormatConditions.Add(Type:=xlCellValue, Operator:=xlGreater, Formula1:="=$G" & i)