VBA Call Function with If Then Statement

[Mo796]

Hello All, I am using a macro that tracks data onto another worksheet as well as calling another function to place an indicator of +/- and color the cell green/ red respectively depending on whether the total net worth calculated increases or decreases. These macros work well on their own, and I have gotten it to run the first part of my if/ then statement, but it does not run the other half of the if/ then. So it always places a - and red. When run alone the if/then works fine so it appears to be an issue with my call function. Am I missing an argument for the function?


Sub Track()

Sheets("Balance Timelines").Select

Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrBelow = 0

Dim sourceWs As Worksheet, dstWs As Worksheet


With Range("B3")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B3").Copy

Call dstWs.Range("A4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B4")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B4").Copy

Call dstWs.Range("B4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B5")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B5").Copy

Call dstWs.Range("C4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("B6")

Set sourceWs = Sheets("Balance Entry Sheet")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("B6").Copy

Call dstWs.Range("D4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("D25")

Set sourceWs = Sheets("Debt")
Set dstWs = Sheets("Balance Timelines")

sourceWs.Range("D25").Copy

Call dstWs.Range("E4").End(xlUp).Offset(1).PasteSpecial(Paste:=xlPasteValues)

End With

With Range("G4")

Set dstWs = Sheets("Balance Timelines")

dstWs.Range("G4").End(xlUp).Offset(1).Value = Now()

End With

Call FormatCells
Call TCalculation

End Sub

Sub FormatCells()

Sheets("Balance Timelines").Select

If Range("F3").Value - Range("F4").Value > 0 Then
Range("H3").Value = "+"
Range("H3").Interior.Color = 5287936
Else
Range("H3").Value = "-"
Range("H3").Interior.Color = 255
End If

End Sub

Sub TCalculation()

Sheets("Balance Timelines").Select

Range("F3") = WorksheetFunction.Sum(Range("A3:D3")) - Range("E3")

End Sub

- - - - - - - - - - - - - - - - - -
The issue I am having is that it is not running this half of my first call, it always formats the cell as - and red:
If Range("F3").Value - Range("F4").Value > 0 Then
Range("H3").Value = "+"
Range("H3").Interior.Color = 5287936

 

[Micron]

yes, as already advised in post 2.

Aside from that, my suggestion would be to put a break point at the start of your code step through. Watch the flow and the values of variables and other things (those lines have to be executed before the values will be set).

I realize some of the code that I mentioned isn't part of your issue. I'm just saying there is no point to it as it is written. If you want to keep it, not a problem. I usually try to teach in my answers when the opportunity arises rather than just trying fix things.