color cell based on increase/decrease in data

B

blue_moon

New Member
Joined
Dec 22, 2010
Messages
11
Hi,

I am working on a spreadheet in which the numerical value in cell A1 are copied and pasted from another worksheet. The value can increase and decrease over time.

Also, the value may sometime have a negative value hence when the numerical number gets higher with a negative sign, its decreasing, and vice versa.

I would like to know if there is a way to change the cell colour based on the increase/decrease data (e.g. green when increase, red when decrease).
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
See if this is any use. I have assumed cell Z1 is available to use as a helper cell. This column could be hidden if you want. To implement the code ...

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering or pasting values into cell A1.

If this doesn't do what you want, please add more detail and/or specific examples.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> iCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> cc <SPAN style="color:#00007F">As</SPAN> Range, sc <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> ChangeCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1"   <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> StoreCell <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Z1"    <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> cc = Range(ChangeCell)<br>    <SPAN style="color:#00007F">Set</SPAN> sc = Range(StoreCell)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, cc) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> IsNumeric(cc.Value) <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> cc.Value - sc.Value<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 0<br>                    iCol = 4<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0<br>                    iCol = 3<br>                <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                    iCol = xlNone<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>            cc.Interior.ColorIndex = iCol<br>            sc.Value = cc.Value<br>            Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Hi Peter_SSs,

Thanks for your help and reply. I have embedded the code into my spreadsheet and it had worked perfectly. I would like to know if I intend to change the colour of more cells, how do I implement that? Do I just change the range from "A1" to say "A1:A2" and the store cell from "Z1" to "Z1:Z2"?

Thanks again

blue_moon
 
Upvote 0
That's slightly more complex because the code needs to cope if more than 1 cell in the target range is changed at the same time. Try this.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> iCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, oSetCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> cr <SPAN style="color:#00007F">As</SPAN> Range, Changed <SPAN style="color:#00007F">As</SPAN> Range, cel <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> ChangeRange <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "A1:A10"  <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Const</SPAN> StoreColumn <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "Z"       <SPAN style="color:#007F00">'<-- Change to suit</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> cr = Range(ChangeRange)<br>    <SPAN style="color:#00007F">Set</SPAN> Changed = Intersect(Target, cr)<br>    <br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Changed <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>        oSetCol = Columns(StoreColumn).Column - cr.Column<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cel <SPAN style="color:#00007F">In</SPAN> Changed<br>            <SPAN style="color:#00007F">If</SPAN> IsNumeric(cel.Value) <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> cel.Value - cel.Offset(, oSetCol).Value<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> > 0<br>                        iCol = 4<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> < 0<br>                        iCol = 3<br>                    <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN><br>                        iCol = xlNone<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>                Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                cel.Interior.ColorIndex = iCol<br>                cel.Offset(, oSetCol).Value = cel.Value<br>                Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cel<br>        Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
Two colours in a single based on positive or negative value
Replies
2
Views
514
montu_seshil
M
M
  • Question
I have been stuck for months
2
Replies
16
Views
909
Fluff
Fluff
K
  • Question
Pause a macro when the user starts entering a number in the active cell & resume it when they hit the Enter key
Replies
3
Views
945
Jaafar Tribak
Jaafar Tribak
J
  • Solved
Assign a value to a range of cells based on the color of conditional formatting
Replies
3
Views
772
6StringJazzer
6StringJazzer
S
  • Question
Using Drop Down menus for data entry
Replies
8
Views
299
Sambrowne
S

Forum statistics

Threads
1,214,874
Messages
6,122,034
Members
449,061
Latest member
TheRealJoaquin

Share this page

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
Back
Top