Is it possible to create a macro for conditional formatting?

[Retired-071445]

I track student progress across their school years for a number year groups. So each student will have scores entered across 12 columns. I use conditional formatting to colour the background of the current year results comparing scores on start of year to current term. I use c formatting with the following code for eg = AND($D7>$A7 +3) turn cells green; = AND($D7=$A7 +3) turn cells orange and = AND($D7<$A7 +3) turn red. I then use format painter to copy to the rows below. However when different columns are involved I have to repeat this process. Is there a means of speeding this up? Any help appreciated.

 

[VoG]

Try selecting the entire range then enter the CF referring to the top left cell in the selection.

 

[Retired-071445]

When I do that it does not formatt correctly. I need the three selected cells to colour the same background colour based on the values of say column d and a. When I select the entire range the formatting does not work but when I do it on the first row of the range and copy down with painter, it does. However it is tedious to repeat this for each year group in the school.