Colour change greater or lower than original

Jolly Roger

New Member
Joined
Oct 7, 2006
Messages
5
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

ChrisUK

Well-known Member
Joined
Sep 3, 2002
Messages
675
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

New Member
Joined
Oct 7, 2006
Messages
5
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

Well-known Member
Joined
Sep 3, 2002
Messages
675
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
 

Forum statistics

Threads
1,136,712
Messages
5,677,335
Members
419,688
Latest member
sarahmichelle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top