Conditional Formatting with multiple formula/criteria

[deboz]

Hi Everyone,

I am having a bit of trouble with conditional formatting. I feel like I am losing my mind!

What my boss wants is the value in cell E to be a certain colour depending on the value.


If H is higher then E, color it green
If H is higher than E by 500 then color it yellow
If H is higher then E by 1000 then color it blue
If Hi s higher than E by 1500 then color it purple

Example:

[TABLE="class: grid, width: 778"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Employee[/TD]
[TD]Earnings[/TD]
[TD]Deduction[/TD]
[TD]Sup[/TD]
[TD][/TD]
[TD]Bonus Paid[/TD]
[TD]User[/TD]
[TD]Revenue (tax incl)[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Name 1[/TD]
[TD] $ 1,206.89[/TD]
[TD] $ - [/TD]
[TD] $ 106.80[/TD]
[TD] $ 3,941.07[/TD]
[TD][/TD]
[TD]Name 1[/TD]
[TD] $ 1,755.40[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Name 2[/TD]
[TD] $ 587.38[/TD]
[TD]-$ 75.00[/TD]
[TD] $ 52.78[/TD]
[TD] $ 1,695.48[/TD]
[TD]75[/TD]
[TD]Name 2[/TD]
[TD] $ 1,730.95[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Name 3[/TD]
[TD] $ 948.95[/TD]
[TD] $ - [/TD]
[TD] $ 88.18[/TD]
[TD] $ 3,111.39[/TD]
[TD][/TD]
[TD]Name 3[/TD]
[TD] $ 1,946.85[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Name 4[/TD]
[TD] $ 1,032.39[/TD]
[TD] $ - [/TD]
[TD] $ 96.51[/TD]
[TD] $ 3,386.70[/TD]
[TD][/TD]
[TD]Name 4[/TD]
[TD] $ 3,015.25[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Name 5[/TD]
[TD] $ 1,173.06[/TD]
[TD] $ - [/TD]
[TD] $ 108.30[/TD]
[TD] $ 3,844.08[/TD]
[TD][/TD]
[TD]Name 5[/TD]
[TD] $ 1,038.80[/TD]
[/TR]
</tbody>[/TABLE]


Can anyone help me please

Regards
Deb

 

[deboz]

I got it to work . I added a formula in column J =H2-E2 then i highlighted column E and added a few conditional formatting rules based on a formula:

=AND(J2>=0,J2<=499) format to green background
=AND(J2>=500,J2<=999) format to yellow background
=AND(J2>=1000,J2<=1500) format to blue background