Hi,

I am slowly building a mark book spread sheet and am trying to colour code the marks that have been entered.

I have attached the sheet in question in the hopes that this will help.

I have used the below in the "H" column which finds the difference between the rank position of the average marks and the rank position of the number in column A.

=((_xlfn.RANK.AVG(A9, ($A$9,$A$11,$A$13,$A$15,$A$17,$A$19,$A$21,$A$23,$A$25,$A$27,$A$29,$A$31,$A$33,$A$35,$A$37,$A$39,$A$41))))-(_xlfn.RANK.AVG(I9, ($I$9,$I$11,$I$13,$I$15,$I$17,$I$19,$I$21,$I$23,$I$25,$I$27,$I$29,$I$31,$I$33,$I$35,$I$37,$I$39,$I$41)))

Is it possible to use the same type of code in a macro?

I would like to be able to either use a macro or just the conditional formatting to compare each mark that I enter to the marks in column A and then colour in bands -5<, -5to5, >5.

Could anyone help with this?

Many thanks in advance,marks.xlsx