Streamlining IF command

[cynicor]

Hi.

Firstly, I have tried a search to no avail, but I am not even sure if I am using the right terms to be honest.
I have an excel spreadsheet, it is actually a roster. Shift times are variable but each day three cells will be coloured differently (so one purple, one blue, one green) and the others all white.
I am looking for a formula that will display the person in colour x for that day.
I have had some success making a function for background colour from the internet, and I can use the IF function for up to 8 of the 10 columns but it then hits the length limit. (Excel 2010) It also seems very unwieldy and would be hard to change easily. I am sure there is a better way of doing this, or by making a VBA function.
My spreadsheet is Columns for person and rows for each day of the week, so below is what I have so far (the person's name is on Row 5):

Please Login or Register  to view this content.

The VBA function (from a website) is

Please Login or Register  to view this content.

Is anyone able to offer any help please?

Thanks,
Jon

[protonLeah]

Hi Jon/cynicor, welcome to the forum. Please take a moment to read the forum rules and then add code tags around your codeHi cynicor, welcome to the forum. Please take a moment to read the forum [URL=

[cynicor]

Thank you for your reply.

I have now read the rules, edited the post and attached a sample of the workbook. The cell with the working formula is S18, but it can only extend to about 8 cells.

Thank you for your time.

[protonLeah]

Try this macro instead of trying to do it with formulas:

Please Login or Register  to view this content.

[cynicor]

Wow, that is amazing.

Thank you for your help.

In trying to solve my problem I tried to learn basic VBS and had made something that works, but it was extremely crude and could not scale. Your simple code looks like it will scale much more effectively.

Do you mind explaining a little about how I would alter the on call names to be on their own sheet, if the roster was over 3 sheets?

What I have, is the sample worksheet 3 times over on different sheets (so actually 30-35 people in 3 different areas) and in the end hope for the oncall to be a 4th worksheet that will be easier for people to read.

In the meantime I will try to understand your code. I am getting the hang of the basics and think it will be a matter of tweaking the "range" values and to where the names actually display

The sample below is a de-identified actual roster to illustrate.

Thank you again for your help.

[cynicor]

EDIT- NEVERMIND- I have fixed it. Was using Shifts when I should have used Shifts2.

So using the code kindly posted above, I have altered it to obtain results on a larger roster. Only problem is it now does not work

I just thought I needed to replicate the original code again, and have figured out how to extend the roster, add more people in, add another on call etc.

The final part is making it work across multiple sheets. It seems to work almost completely but for some reason does not like to print the names of the person from the second sheet. It instead gives a runtime 91 error, and I can't figure out why.

Any ideas?

Please Login or Register  to view this content.

Debugging highlights the last oncall4 offset line.

Thanks!

EDIT- NEVERMIND- I have fixed it. Was using Shifts when I should have used Shifts2.

Thank you for such a helpful forum