VBA Conditional Formatting

[Mo796]

I am not sure how to achieve my desired result and have not yet found a code combination that works. I'm pretty much learning as I go and have not used conditional formatting in VBA yet. I am tracking balances from one tab (Balance Entry Sheet) to another tab (Balance Timelines). The current macro shifts all cells down and pastes the newly collected data on the new first line of the receiving tab. I want the most recent capture to show on the top row of the new page for convenience, which the macro currently accomplishes. However, my issue is that I currently have column H on the receiving tab conditionally formatted to calculate the difference between the current cell and the cell below it in column F and fill the cell color in column H either green or red and corresponding with a value "+" or "-" based on whether the calculation determines the value has gone up or down. IE "=IF((F3-F4)>0,"+","-")" and so on. Since my macro creates a new line when copying and pasting the data from another tab I lose the conditional formatting calculation and end up with a blank cell in row H. How do I best ensure this conditional formatting is also applied to the new row? Below is my current macro.


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 sourceWs = Sheets("Future Projections")
Set dstWs = Sheets("Balance Timelines")

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

End With

End Sub

 

[Jamie McMillan]

Hello,

After running your code add in the following.

Sub FormatCells()
    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

Since the VBA doesn't change it will always look at cells F3 & F4 and colour H3

Jamie

 

[Mo796]

Hello,

After running your code add in the following.

Sub FormatCells()
    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

Since the VBA doesn't change it will always look at cells F3 & F4 and colour H3

Jamie

As my macro is running on a different worksheet I just added Sheets("Balance Timelines").Select under Sub and I can have it on both worksheets. Thank you for assisting me!