How to copy conditional formatting with formulas

[MaddogJason]

A small Saturday evening challenge for you.
I've done some conditional formatting with a formula. Works like a charm.
Based on the value in cell E4 the range E5:E6 changes colour.
Now I wish to copy/paste or drag the formula to be valid in the next column. but based on F4.
This seems to work but it will not automatically change the reference to the next column. I played around with the dollar signs but it did not seem to help.
The final idea would be to have different colours for a range of values based on the value in row 4, but each column with reference to its own row 4.

CocoaBreak3.0.xlsm
	B	C	D	E	F	G
2		Grade	Grade avg	alfa	Beta	Ch.2. Test
3
4	Last name		Weigh->	1	2	2
5	Lisa		6.7	6	7	6.5
6	Paul		5.9	4	7	5
Grades (2)

Cell Formulas
Range	Formula
D5	D5	=SUMPRODUCT(E$4:AA$4*E5:AA5)/SUMIF(E5:AA5,"<>",E$4:AA$4)
D6	D6	=SUMPRODUCT(E$4:R$4*E6:R6)/SUMIF(E6:R6,"<>",E$4:R$4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E2	Expression	=$E$4<=1	text	NO
E5:E23	Expression	=$E$4<=1	text	NO

 

[Fluff]

Select the range you want to use it on & use this formula

=E$4<=1

 

[MaddogJason]

Select the range you want to use it on & use this formula

=E$4<=1

PFFFFT! I swear to the Excel God´s that I gave that option a go, and it did not work!
Then you post the advice and I give it another go and.... voila!
Once again Cheers for the help Fluff!
I have a feeling We´ll talk again

 

[Fluff]

You're welcome & thanks for the feedback.