Set VBA Conditional Format highlight cell not equals 0 and amend subtraction to formula

[hrh_dash]

How do i amend the Macro to 

1. set VBA Conditional Format highlight cell when cell is not equals 0 and

2. amend dynamic subtraction to formula instead

Below is the code:

Sub checkcal()

Dim check As Long
Dim ws As Worksheet
Dim receiptsLastrow As Long
Dim paymentsLastrow As Long
Dim contractbid As Range
Dim vendortotal As Range

Set ws = Sheet1

check = ws.Cells(ws.Rows.Count, "G").End(xlUp).row

ws.Range("G" & check + 2).Value = "Check"
ws.Range("G" & check + 3).Value = "Check receipts ties back to contract bid"
ws.Range("G" & check + 4).Value = "Check payments ties back to vendor cost"

receiptsLastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).row
paymentsLastrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).row

If ws.Range("H1") = "Yes" Then

ws.Range("G" & check + 3).Offset(0, 1).Value = (Cells(receiptsLastrow, "B").Value) - (ws.Range("H2") + ws.Range("H4"))
ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Cells
Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

End With

ws.Range("G" & check + 4).Offset(0, 1).Value = (Cells(paymentsLastrow, "C").Value) - (vendortotal.Offset(0, 3).Value)
ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"

Else

With ws.Cells
Set contractbid = .Find(What:="Contract Bid (incl GST)", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
       
 End With
            
ws.Range("G" & check + 3).Offset(0, 1).Value = (Cells(receiptsLastrow, "B").Value) - (contractbid.Offset(0, 1).Value) '<-- to show formula
ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 3).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
    .Color = vbRed
    
End With
End With

With ws.Cells
Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

End With

ws.Range("G" & check + 4).Offset(0, 1).Value = (Cells(paymentsLastrow, "C").Value) - (vendortotal.Offset(0, 3).Value) '<-- to show formula
ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 4).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlExpression, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
    .Color = vbRed
    
End With
End With

End If
End Sub

[#2 · Accepted solution]

@hrh_dash 

1. About conditional formatting: the first one looks OK, but in the second one you should change xlExpression to xlCellValue (as in the first one)

2. For the formulas:

ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & contractbid.Offset(0, 1).Address(False, False)

and

ws.Range("G" & check + 4).Offset(0, 1).Formula = "=C" & paymentsLastrow & "-" & vendortotal.Offset(0, 3).Address(False, False)

[#3]

Hi @Hans Vogelaar , as for this formula below,

ws.Range("G" & check + 3).Offset(0, 1).Value = (Cells(receiptsLastrow, "B").Value) - (ws.Range("H2") + ws.Range("H4")) 

 i tried to amend to the one below but its popping out an error. How should i amend it to show the formula?

ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & (H2 & "+" & H4) ?

and also not sure why the conditional formating code was not executed; is it right to include the conditional formating code in both after IF and after Else?

this is the revised code:

Sub checkcal()

Dim check As Long
Dim ws As Worksheet
Dim receiptsLastrow As Long
Dim paymentsLastrow As Long
Dim contractbid As Range
Dim vendortotal As Range

Set ws = Sheet1

check = ws.Cells(ws.Rows.Count, "G").End(xlUp).row

ws.Range("G" & check + 2).Value = "Check"
ws.Range("G" & check + 3).Value = "Check receipts ties back to contract bid"
ws.Range("G" & check + 4).Value = "Check payments ties back to vendor cost"

receiptsLastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).row
paymentsLastrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).row

If ws.Range("H1") = "Yes" Then

ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & (H2 & "+" & H4)
ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 3).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
    .Color = vbRed
    
End With
End With

With ws.Cells
Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

End With

ws.Range("G" & check + 4).Offset(0, 1).Formula = "=C" & paymentsLastrow & "-" & vendortotal.Offset(0, 3).Address(False, False)
ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 4).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
   .Color = vbRed

End With
End With

Else

With ws.Cells
Set contractbid = .Find(What:="Contract Bid (incl GST)", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
       
 End With
            
ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & contractbid.Offset(0, 1).Address(False, False)
ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 3).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
    .Color = vbRed
    
End With
End With

With ws.Cells
Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)

End With

ws.Range("G" & check + 4).Offset(0, 1).Formula = "=C" & paymentsLastrow & "-" & vendortotal.Offset(0, 3).Address(False, False)
ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"

With ws.Range("G" & check + 4).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
    .FormatConditions.Delete
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
     Formula1:="=0"
With Selection.FormatConditions(1).Interior
    .Color = vbRed
    
End With
End With

End If

End Sub

[#4]

@Hans Vogelaar ,

i managed to did a workaround by replacing conditional formating code with an IF Else function and the formula:
ws.Range("G" & check + 3).Offset(0, 1).Value = (Cells(receiptsLastrow, "B").Value) - (ws.Range("H2") + ws.Range("H4"))

to be replace with the code below:

With ws.Cells
Set contractbid = .Find(What:="Contract Bid (incl GST)", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
       
 End With

With ws.Cells
Set depositamt = .Find(What:="% Deposit of contract sum:", After:=ActiveCell, LookIn:=xlValues _
        , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
       
 End With
 
ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & contractbid.Offset(0, 1).Address(False, False) & "-" & depositamt.Offset(0, 1).Address(False, False)

thanks and appreciate the guidance.

[#5]

@hrh_dash 

The line with the formula should probably be

ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-H2+H4"

Without seeing the workbook it's impossible to tell why the conditional formatting isn't applied.

P.S. Please indent your code consistently. It is very difficult to read it without proper indentation.

Sub checkcal()
    Dim check As Long
    Dim ws As Worksheet
    Dim receiptsLastrow As Long
    Dim paymentsLastrow As Long
    Dim contractbid As Range
    Dim vendortotal As Range
    Set ws = Sheet1
    check = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    ws.Range("G" & check + 2).Value = "Check"
    ws.Range("G" & check + 3).Value = "Check receipts ties back to contract bid"
    ws.Range("G" & check + 4).Value = "Check payments ties back to vendor cost"
    receiptsLastrow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
    paymentsLastrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    If ws.Range("H1") = "Yes" Then
        ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-H2+H4"
        ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"
        With ws.Range("G" & check + 3).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
             Formula1:="=0"
            With Selection.FormatConditions(1).Interior
                .Color = vbRed
            End With
        End With
        With ws.Cells
            Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
                , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
        End With
        ws.Range("G" & check + 4).Offset(0, 1).Formula = "=C" & paymentsLastrow & "-" & vendortotal.Offset(0, 3).Address(False, False)
        ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"
        With ws.Range("G" & check + 4).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
             Formula1:="=0"
            With Selection.FormatConditions(1).Interior
               .Color = vbRed
            End With
        End With
    Else
        With ws.Cells
            Set contractbid = .Find(What:="Contract Bid (incl GST)", After:=ActiveCell, LookIn:=xlValues _
                , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
        End With
        ws.Range("G" & check + 3).Offset(0, 1).Formula = "=B" & receiptsLastrow & "-" & contractbid.Offset(0, 1).Address(False, False)
        ws.Range("G" & check + 3).Offset(0, 1).NumberFormat = "#,##0.00"
        With ws.Range("G" & check + 3).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
             Formula1:="=0"
            With Selection.FormatConditions(1).Interior
                .Color = vbRed
            End With
        End With
        With ws.Cells
            Set vendortotal = .Find(What:="Total", After:=ActiveCell, LookIn:=xlValues _
                , Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)
        End With
        ws.Range("G" & check + 4).Offset(0, 1).Formula = "=C" & paymentsLastrow & "-" & vendortotal.Offset(0, 3).Address(False, False)
        ws.Range("G" & check + 4).Offset(0, 1).NumberFormat = "#,##0.00"
        With ws.Range("G" & check + 4).Offset(0, 1) '<-- condition formatting to fill cell in red if cell value <> 0
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
             Formula1:="=0"
            With Selection.FormatConditions(1).Interior
                .Color = vbRed
            End With
        End With
    End If
End Sub

[#6]

@Hans Vogelaar , turns out the IF Else did not solve the issue. Managed to use the macro recorder to tweak the conditional formatting to be dynamic.

Sub checkcal()
    
    Dim lastRowH    As Long
    
    Set ws = Sheet1
    
    lastRowH = ws.Cells(ws.Rows.Count, "G").End(xlUp).row
    
    ws.Range("H" & lastRowH).Offset(-1).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                                   Formula1:="=0"
    With Selection.FormatConditions(1).Interior
        .Color = 255
    End With
    
    ws.Range("H" & lastRowH).Select
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlNotEqual, _
                                   Formula1:="=0"
    With Selection.FormatConditions(1).Interior
        .Color = 255
    End With
        
End Sub