VBA Needed for Color Coding A cell based on the cell to the left of it

[nrguerrieri]

I need a vba code that will color code the cell based on the cell to the left of it, previously cell (this is a table with say jan 2010 and the cell to the right will be feb 2010 and so forth)

For ex.

A1 and A2 can be any #. So for example A1 says -.79% and A2 says 0.75%. Since A1 to A2 was an increase. the cell in A2 will need to be green (>10% so a2 will need to be dark green - i explain further below)

Rules: The move from jan to feb (a1 to a2) increases It needs to be green (showing an increase). Vice versa, if the cell in a1 is more than a2, A2 needs to autofill color to red. Important to note that the code needs to be written that it is based on only the left adjacent, to the left of it because i need to replicate this in 100 other columns.

More rules. If its green and the increase is less than 10% of the value to the left = light green. If the cell to the right increased more than 10% than the left cell = dark green. Vice Versa as well. If the decrease was not more than 10% a light red, if the decrease was more than a 10% decrease = a darker red
+ if the cell moved either an increase or decrease of more than 50% please make it so there is no autofill = white

See pic for understanding


**Once you create the code I will be able to tailor (in the future) the colors i need and the difference parameters should i feel like changing the rules from like 50% to like 40% for example.

 

[JumpingCrab]

I think you can just use conditional formats.
A lot simpler than writing VBA code for this.

Your conditional format checks run from high to low (or vice versa) and you stop if a condition is true. See example below.

 

[nrguerrieri]

Thanks for the reply.. but is it based on the cell to the left of each continuing cell? If I do it this way.. Wont i need to enter this in for evert two set of cells. That would means i would have to do this over 200 times?

Thanks,

 

[JumpingCrab]

No, you don't have to enter this for every cell pair.
So one way to do this, is to select the entire set of cells that need the conditional format. Then add the conditional formats one by one (keep the cells selected). As you can see in my example, the formula does not use absolute ($) cell references. The formula is I23>H23 and not $I$23>$H$23. Using relative references make the conditional format formula adjust for each cell in the row.
The other way, is to set the conditional formats up for the first cell in the row (in the same way as above) and then just copy / paste formats to the rest of the cells

Just play around with it a bit. You'd be impressed what you can do with conditional formats. I use them a lot.