Colour change greater or lower than original

[Jolly Roger]

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.
I read up about conditional formatting and don't know the exact formula to use.

 

[ChrisUK]

Hi and welcome to the board

I used this method, it seems to work,. I didn't add the background shading but I hope you'll get the idea.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target > Worksheets("sheet2").Cells(1, 1) Then
MsgBox ("Greater")
Else
MsgBox ("Less")
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Worksheets("sheet2").Cells(1, 1) = Target

End Sub


HTH

Chris

 

[Jolly Roger]

Thanks. I tried this and I got an run time error 9 script out of range. I don't rally understand what this script is doing.
Is there a simpler way by using conditional formatting?

 

[ChrisUK]

The runtime error must be because you don't have a second sheet or if you do it's not called "Sheet2"

The reason you have to do it this way is because you need to get the value of the cell BEFORE the change is made. I'm doing this by trapping the event which is triggered when you change the current selection (i.e. move to another cell) and then copying the contrents on this cell to a different worksheet (you have to do this or the change event will trigger in the current worksheet)

the change event which triggers when you CHANGE the currently selected value will then compair the previous and the new value and generate a message box telling you if the value is greater then or less than the previos value.

I suppose you could do this with a variable as well ... sorry, I shoud have thought of this last night but it was late ... thats my excuse anyway.

Try this code.

Dim PrevValue As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PrevValue = Target
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target > PrevValue Then
Target.Interior.ColorIndex = 6
Else
Target.Interior.ColorIndex = 20
End If
End Sub


You might need to alter the colorindex value to suit your required colors, if you're unsure about what colours to use record a new macro and fill them from the toolbar, then look at the number Excel defines in the new modual VB will create

Hope this helps.

Chris

 

[Jolly Roger]

This works brilliantly. Many thanks for your time - it is much appreciated.
Rog