Colour cell when number changes

[Guest]

If the number in a cell is changed and it is larger than the original number
I want the cell to be highlighted in a green colour. If the number is lower
than the original I want the cell to be highlighted in red.
Any suggestions gratefully accepted.
Roger

 

[Guest]

Hi Jolly:

Put the following in worksheet code:

Dim v
Private Sub Worksheet_Activate()
v = Cells(1, 1).Value
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1")) Is Nothing Then
Exit Sub
End If
If Cells(1, 1).Value > v Then
Cells(1, 1).Interior.ColorIndex = 50
End If
If Cells(1, 1).Value < v Then
Cells(1, 1).Interior.ColorIndex = 3
End If
End Sub


When the sheet is activated, the first macro just records the value in A1 in
a static variable v. If A1 changes, the second macro compares the new value
to v and colours the cell in an appropriate fashion.

 

[Guest]

--
Gary''s Student

 

[steveK]

Holy Macro!

Would this get put into visual basic editor?

 

[Gord Dibben]

Right-click on your worksheet tab and "View Code".

Copy/paste into the sheet module that appears.

Alt + q to close.


Gord Dibben MS Excel MVP

 

[Guest]

Hi,
I pasted this into the sheet. It works for cell A1. How do I use it on the
whole sheet?

 

[Gord Dibben]

Use the formatting paintbrush to copy the formatting to cells you want with that
CF


Gord Dibben MS Excel MVP