Conditional formula based on previous columns

[beternal]

This is probably a nice easy one... (three questions/parts)

Table of numbers.

1:
If values in column B = 0, colour green,
If values in column B > column A, colour red
If values in column B < column A (but <>0), colour amber

2:
How do I copy the above so the same rules apply in respective columns, i.e. B compared to A, C compared to B, D compared to C... I tried format painter with no luck (am I doing it wrong?)

3:
How do I colour scale the above formulae?
i.e.:
If column B is 0, colour green,
If values in column B ≥ column A, colour amber to red based on the previous values in that row
If values in column B ≤ column A, colour amber to green based on the previous values in that row

...and likewise copy to other cells

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Office 2006 or MS365? Please update your profile if the latter.

[beternal]

Edit: please find attached random grid of numbers to appease admin

[JeteMc]

As to numbers 1 and 2:
1. Change the formula in A1:E20 to read: =RANDBETWEEN(1,30) so that there will be some zero values with which to test the conditional formatting.
2. Select cells B1:E20
3. Use formulas for conditional formatting rules > Note that the rule for green needs to be listed before the rule for amber.
Green: =B1=0
Amber: =B1<A1
Red: =B1>A1
As to number 3 I don't feel that it is possible to scale the colors the way that you want using conditional formatting rules. Perhaps someone whom is fluent in VBA can come up with a solution.
Let us know if you have any questions.