oh, i see what you meant now.
That's the problem with these huge formulae, easy to lose track lol!
Hopefully this should do it then:
Code:Green formatted cell: =IF(left(A1,1)<left(B1,1),1,if(left(A1,1)>left(B1,1),0,if(right(A1,1)>right(B1,1),1,0))) Amber formatted cell: =IF(left(A1,1)=left(B1,1),if(right(A1,1)=right(B1,1),1,0),0) Red Formatted cell: =IF(left(A1,1)>left(B1,1),1,if(left(A1,1)<left(B1,1),0,if(right(A1,1)<right(B1,1),1,0)))
j17sparky (21st June 2010)
Brilliant, that works!
It would also seem that you can copy down conditional formatting! Thats how long i havnt used excel for when 2003 has a feature i didnt know about!
Thanks alot for your help, and everyone else. I do prefer this method rather than vLookups and vb as it should be abit more resiliant to teachers messing around with the worksheet.
Last edited by j17sparky; 21st June 2010 at 01:53 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)