VBA Conditional formatting, if loop

[frustrated_macro]

i need to create a macro that basically does this kind of conditional formatting

the range will be G2:G & LAstRow, which has already been defined, the range in the screenshot was just me testing that all the colors worked
but i have no idea how to write that into the macro. i had a very sad attempt at an IF loop, but im not sure how to properly write the formula AND the formatting into the loop.

 

[DanteAmor]

And if you only increase the range of cells to apply, for example, assuming that you will have a growth range up to 10000 data

=$G$2:$G$10000

 

[frustrated_macro]

And if you only increase the range of cells to apply, for example, assuming that you will have a growth range up to 10000 data

=$G$2:$G$10000

Right, im not too worried about the range. what i need help with is how to convert that formatting (the formula and the color) into VBA code so i can put it in my macro

 

[DanteAmor]

Try this:

Sub Macro1()
  Dim lr As Long
 
  lr = Range("G" & Rows.Count).End(3).Row
 
  Cells.FormatConditions.Delete
  '
  With Range("G2:G" & lr)
 
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2>$G2,G2>0,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5263615
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False

    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($G2>$K2)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13421823
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
   
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2=$G2,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13434879
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
 
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2=0,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 11854022
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

 

[frustrated_macro]

Try this:

Sub Macro1()
  Dim lr As Long
 
  lr = Range("G" & Rows.Count).End(3).Row
 
  Cells.FormatConditions.Delete
  '
  With Range("G2:G" & lr)
 
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2>$G2,G2>0,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 5263615
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False

    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($G2>$K2)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13421823
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
  
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2=$G2,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 13434879
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
 
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2=0,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 11854022
        .TintAndShade = 0
    End With
    .FormatConditions(1).StopIfTrue = False
  End With
End Sub

THIS WORKEDDDDDDDDDDD THANK YOUUUUUUUUUUUUUUUUUUUUUUU

 

[rlv01]

One minor comment. The statement deleting any existing format conditions:

  Cells.FormatConditions.Delete

will delete all format conditions in the worksheet, and if that's what you want , then all is good. However if you have other format conditions on the worksheet that you do not want to delete then it is better to restrict the delete statement to just the range of cells that you are formatting.

  With Range("G2:G" & lr)
    .FormatConditions.Delete

    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND($K2>$G2,G2>0,G2>0)"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority