IF color THEN value

Here's a macro that can be executed via a button on the sheet. But note that if there are multiple cells with that color only the last referenced name will be copied to B4 on sheet3. Do you want the code to do something different?

Please Login or Register  to view this content.

Thank you so much for your prompt reply! But yes, I'm afraid I explained it rather poorly.

If you look at sheet 1 in the above file, which is used for scheduling, you can see there are employee names in the A column. Employees are on call when they are marked with the pink/redish color. I would like to have a macro on another sheet which states which worker is on call for a specific week (defined by who is on call on the first day of the week). For example, day 1 of week 1 "Name 3" in department 1 would be on call, thus I would like a macro that in B4 on the second sheet states "Name 3". On day 1 of week 3, the call is on "Name 6", thus I would like to B8 in the second sheet to say "Name 6" because of the color in the schedule.

If possible, the best thing would be if I could find a macro that searches through both departments (i.e. for example B7:B17 AND B20:28) but only places the first match in B4 on the second sheet, and the second match in C4.

Here you go. I tried to compute all duty cycles for you, but the data for duty 3 is not consistant.

Please Login or Register  to view this content.

Before progressing with a more complex VBA approach, would alternatively suggest to simplify the model structure and keep formula based.

For B7:AT17, instead of hard coding 1s + manual colour formatting, inputs could be tied to unique numbers for each task/duty (e.g. 1, 2, 3) + conditional formatting. For subtotal rows 30-32 use COUNTIF, row 34 use COUNT. If output sheet is tied to a specific day/column, simple INDEX MATCH would do.