Hello Dav and Franz,
First of all thanks for your response, I have tried what you have both
suggested and what you said Dav about using Franz’s suggestion for the
first 34 and then changing it slightly made good sense as soon as I had
read it and to be honest with you it was something that I have no even
thought of. I have already tried using countif(b2:aj2)=3 plus a few
other ideas but kept running into problems. Ok now to get back to what
you guys suggested. Ok I have tried it but again a few problems;
1st Franz I changed your suggestion to 2 rather then 3.
2nd Everything appears to work good but after entering the 3rd late
all the cells change colour from the last entry to forward. I will try
to explain a little bit better. Ok for example if Anna is late on the
8th (cell I2) and 10th (cell K2) June and then again late on the 11th
(cell L2) June all the cells from the 11th June to the 12th (cell AQ2)
July change colour. But the cells from the 8th June and up to the cell
before the last late do not change colour. But if you count all the
cells from the 1st late to the last coloured cell 8th July it will
equal 35 exactly, so I thought that is a good start.
But now after trying all of this I thought of a good idea and that is
if we can not get the above to work perfect. I will go with the
following. So if I changed the formula to be 1. Now what happens is if
someone is late 2 times all the cells will light up from the last late
for example if I use the example above (with the 3 lates) to the 8th
July and the managers then give that staff member a bit of a warning
“If you are late any time now during this period which is highlighted
you will be receiving a fine”.
But of cause if you guys can come up with a good solution I will give
it ago. But to be honest with you I have been working on this now for
sometime and I normally do not give up. But both you guys have put me
onto the right track and for that thanks very much.
Paul Maynard
--
paulrm906
------------------------------------------------------------------------
paulrm906's Profile:
http://www.excelforum.com/member.php...o&userid=28776
View this thread:
http://www.excelforum.com/showthread...hreadid=555488