Highlighting value if appears in 3 adjacent columns (across different rows)

Hi there

Please forgive me if this is the wrong subforum, I’m not sure if my problem can be solved with a formula/conditional formatting or whether it will require some VBA. I’m completely new to coding & macros so while hoping it can be done with the former, I’m keen to learn VBA so if it’s required I will see this as an opportunity!

We have a rota that allocates each member of staff a different task hour by hour. Staff work 4 different shifts, and the exact task allocation depends on the number of people who are in on each shift. Assuming no more than one person is sick off each shift, each the values in I3:P29 adjust so that each staff member has a task each hour of their shift, and all the necessary posts are covered. This is done by a series of IF functions that check whether or not each shift is fully staffed.

On a day to day basis other staff, who know even less about Excel than I do, need to go in and make the odd manual adjustment by writing over the formula with a text name. I’m looking for a way to highlight if those adjustments lead to the same member of staff being put on till duty for 3 or more hours in succession (they might be on different tills, so I need to check across different rows). So what I need a formula (or code) to do is check if the same name appears in 3 adjacent columns, but just within the range I3:P10 (the till range). If possible I need Excel to be able to recognise names that are overwritten as text alongside those that result from the IF formulas. At the moment each column uses conditional highlighting to display if any names duplicate within the same hour (e.g., at 11am Vic does some chilled but also has ½ hour lunch).

In the attached example, Simplified rota example.xlsx Jennifer is on the till for 3 hours in a row, and her entry in K3 is a manual entry. My hope is to automatically highlight (or colour text for) each of those 3 instances of Jennifer, but not the subsequent entry where she is on lunch break (L26). Ideally she will not be highlighted on her later till duty (N7) but it’s easier that would be ok too.

If it’s not possible to compare manual entries with values resulting from formulas, I can try to restrict staff to using formulas (e.g. typing =B8 to put Jennifer into K3), but it won’t be popular!

Thanks in advance for any help, I’d be happy to follow any directions to any similar solved problems, but I’ve been searching for hours and not found anyone doing exactly what I’m trying. It’s not a conventional use of Excel I guess ;-)

Best wishes

Lucy Magoosey

Hi Jakobshavn

Thanks for reviewing my post, however I don't see the solution: Sorry if I have been unclear, but the main thing I need to do is highlight (or otherwise mark) Jennifer (or anyone else) who is put on till duty (in range I3:P10) for 3 or more consecutive hours. The maximum we like staff to do on the checkouts is 2 consecutive hours, or they start going a bit loopy. They may do 4 or even 5 hours over the whole day but I need to highlight if someone is put down for 3 consecutive hours. They can appear anywhere from till 1-7.

In your example I am able to select staff from a drop down list but I don't see any way to highlight someone who is on till duty for those 3 hours. Also I am already using data validation in our working (not simplified) version to warn people against overwriting formulas before the day the rota is used.