Displaying symbol on increase/decrease in cell value

[dknowles2004]

I'm not sure if this is do-able but I have a numeric value in cell D50. I'd like to make the background cell turn RED when the value decreases and GREEN when it increases.

Additionally, id like to display the word UP (for increase) and DOWN (for decrease) in cell D51.

Does anyone have any ideas on this?

Thanks.

 

[Andrew Poulsom]

What causes D50 to change?

 

[dknowles2004]

D50 contains a =sum(X:Y) formula so sums the values in column D.

Thanks.

 

[Andrew Poulsom]

Right click the sheet tab and choose View Code. Paste this into the window on the right.

Private Sub Worksheet_Calculate()
    Static OldValue As Double
    With Range("D50")
        If IsEmpty(OldValue) Then OldValue = .Value
        If .Value = OldValue Then
             Exit Sub
        ElseIf .Value < OldValue Then
            .Interior.ColorIndex = 3
            .Offset(1, 0).Value = "DOWN"
        Else
            .Interior.ColorIndex = 10
            .Offset(1, 0).Value = "UP"
        End If
        OldValue = .Value
    End With
End Sub

Press Alt+F11 to return to your worksheet.

 

[dknowles2004]

Thanks for you help. If I wanted to do this for multiple columns (eg. D50, F50, H50 etc) can I incorporate this into the code and how?

Thanks.

 

[Andrew Poulsom]

Try this:

Private Sub Worksheet_Calculate()
    Static OldValues(1 To 3) As Double
    Const ColStep As Integer = 2
    Dim c As Integer
    Dim i As Integer
    c = 1
    With Range("D50")
        For i = LBound(OldValues) To UBound(OldValues)
            If IsEmpty(OldValues(i)) Then OldValues(i) = .Cells(1, c).Value
            If .Cells(1, c).Value < OldValues(i) Then
                .Cells(1, c).Interior.ColorIndex = 3
                .Cells(1, c).Offset(1, 0).Value = "DOWN"
            ElseIf .Cells(1, c).Value > OldValues(i) Then
                .Cells(1, c).Interior.ColorIndex = 10
                .Cells(1, c).Offset(1, 0).Value = "UP"
            End If
            OldValues(i) = .Cells(1, c).Value
            c = c + ColStep
        Next i
    End With
End Sub

To add more columns increase the upper bound of OldValues.