Excel - Change Cell Color based on increase or decrease of previous value

Question

Hi and thanks in advance for your time,

In COLUMN B I have a currency value based on a currency value in COLUMN A:

B1 = A1*1.15

Since the values of COLUMN A: fluctuate weekly and have to be manually inputted: I would like to know if there is an increase or decrease in COLUMN B - I'd like to indicate if there was an increase or decrease with a color.

The issue that I'm running into is that excel doesn't seem to like to reference the same cell's value.

possible duplicate of [Excel conditional formatting based on cell and adjacent cell values](http://stackoverflow.com/questions/16175007/excel-conditional-formatting-based-on-cell-and-adjacent-cell-values) — guitarthrower, Mar 03 '15 at 21:40
Hmm... I read that he would like to see if the value of the previous row is different than the current row, but it seems I misread. — guitarthrower, Mar 04 '15 at 16:19

score 1 · Answer 1 · edited May 23 '17 at 12:12

If I understand your question properly, you want to manipulate the colour of column B when you enter a new number if the number in column A has increased or decreased in value from the previously entered number, the formula isn't anything to do with it?

I just posted on how to determine the previous value of a cell here: Detecting what value was in a cell prior to a change

You could apply that like this at the worksheet level:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant, NewValue As Variant
'If in a column other than A then end
If Not Target.Column = 1 Then End
'If the entered value is not numeric then clear shade and end
If Not IsNumeric(Target.Value) Then
    Target.Offset(0, 1).Interior.Pattern = xlNone
    End
End If
'Populate NewValue with value of target
NewValue = Target.Value
'Turn the events off
Application.EnableEvents = False
'Undo the change
Application.Undo
'Populate OldValue with the undone value
OldValue = Target.Value
'Make the target the NewValue once again
Target.Value = NewValue
'Do a comparison on NewValue and OldValue
If NewValue > OldValue Then
    'Shade Green
    With Target.Offset(0, 1).Interior
        .Pattern = xlSolid
        .Color = 5287936
    End With
ElseIf NewValue < OldValue Then
    'Shade Red
    With Target.Offset(0, 1).Interior
        .Pattern = xlSolid
        .Color = 255
    End With
Else
    'Clear shade
    Target.Offset(0, 1).Interior.Pattern = xlNone
End If
Application.EnableEvents = True
End Sub

Thanks Gary, That was probably the most fun little thing I have slapped together in a long time :) — Dan Donoghue, Mar 03 '15 at 22:14

Excel - Change Cell Color based on increase or decrease of previous value

1 Answers1