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:

	A	B	C	D	E	F	G	H
1	Employee	Earnings	Deduction	Sup		Bonus Paid	User	Revenue (tax incl)
2	Name 1	$ 1,206.89	$ -	$ 106.80	$ 3,941.07		Name 1	$ 1,755.40
3	Name 2	$ 587.38	-$ 75.00	$ 52.78	$ 1,695.48	75	Name 2	$ 1,730.95
4	Name 3	$ 948.95	$ -	$ 88.18	$ 3,111.39		Name 3	$ 1,946.85
5	Name 4	$ 1,032.39	$ -	$ 96.51	$ 3,386.70		Name 4	$ 3,015.25
6	Name 5	$ 1,173.06	$ -	$ 108.30	$ 3,844.08		Name 5	$ 1,038.80

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

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