Help with conditional formatting based on cell values

Hello everyone, I'm new to this forum and I'm after you help. I'm self taught at excel and I'm now getting to the stage where I can't solve problems by leaching forums.

I have created a workbook which uses conditional formatting based on a specific cell value. I am making the book to measure pupil progress.

I have done this by going into the "Edit Formatting Rule" within conditional formatting.

I have selected "Format all cells based on their values"

Then using a 3 colour scale I have created values based on "minimum", "midpoint" and "maximum" - all using the number

format1.JPG

The reason for doing this is so that I can see if progress made is red, amber or green or a variation of the colours based on this colour scale.

This formula works for the specific row but if I want to use it for the next row then I have change the values after copying them. I do this by copying the cell, then going back into the "Edit Formatting Rule" within conditional formatting I change the cell values to make them relevant to that row. This has to be done manually for each row.

format2.JPG

I have now done this for 100 rows and have completed it 6 times (for each half term of the school year). This took me quite a while! What I now want to do is create an even bigger database with drop down menus. However I would need to change the conditional formatting manually for a minimum of 1800 rows and I just don't have the time for that. Therefore, my question to this community is "Is there any formula I can use which would automatically change the cell reference to the next row" e.g. =$F$2+(($G$2/12)*1) would become =$F$3+(($G$3/12)*1) automatically when copied without having to manually change the 2 to a 3.

Thanks in anticipation

Hi, When I try that it comes up with the message:

format3.JPG

Instead of colour scales, use formulae.

Select all of the cells to apply it to, and use

Green: =J2>=($F2+(($G2/12)*2))
Amber: =J2>=($F2+(($G2/12)*2))
Red: =J2>=($F2+(($G2/12)*2))

where J2 is the first cell in the target range. Make sure they are applied in that order.

Originally Posted by TMS

Just remove the dollar sign in front of the row number. For example, instead of $F$2, you would put $F2.

The dollar sign makes that part of the cell address absolute.

Regards, TMS

Did you try that? I did, and it said I cannot use relative formulae.