Color cell depending on it's previous value

[Simo]

I would like to make a cell changing color depending on a comparison between
its previous and current values.

Say I would like to start with green background for the cell whatever the
value is, then if the new (calculated or input) value of the same cell is
above the previous one the background stays green, but if the new value is
less that the previous then the background turns to red.

this is somehow a recursif conditional formatting, is this possible?

Many Thanks in advance.
Simo

 

[Gary''s Student]

If the value changed from 10 to 5 and then later to 7, would you want the
color to become red and then go back to green??

 

[Simo]

Yes Absolutly. any idea how to do this?

If the value changed from 10 to 5 and then later to 7, would you want the
color to become red and then go back to green??

 

[Gary''s Student]

It can easily be done with an Event macro. You would need a different macro
for a calculated cell as opposed to an input cell. Let's look at the input
case. We will use cell B9 as the sample. In the worksheet code area, put
the following macro:

Dim OldValue As Variant
Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set b9 = Range("B9")
If Intersect(t, b9) Is Nothing Then Exit Sub
If IsEmpty(OldValue) Then
OldValue = b9.Value
Exit Sub
End If
Application.EnableEvents = False
If b9.Value < OldValue Then
b9.Interior.ColorIndex = 3
Else
b9.Interior.ColorIndex = 50
End If
OldValue = b9.Value
Application.EnableEvents = True
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Simo]

Excellent, thank you Sir.

do you have an example with calculated cell.

I will look at the sites you mention later.

Best Regards
Simo