IF statement based on cell color

Ive been researching whether or not you can write an IF Statement based on the background color of a cell. I found this thread in the forum but I'm not grasping the conclusion. I havent had much luck elsewhere either.

Attached is a sample worksheet showing a master schedule with color coding. Each color represents a different person.

I would like to be able to execute a macro that will create a new page for each color in essensce giving me seperate pages for each person. This way I do not have to share each persons schedule with the other.

A few curve balls.

1. Some cells are merged to represent 1 hour.

2. Some time slots have two colors next to each other representing two meetings at the same time.

I believe I am covering everything but if not please feel free to shoot me a note.

Hi there,

Take a look at the attached workbook and see if it does the sort of thing you're looking for.

The only changes I've made to the workbook itself are to define the "Colour Legend" cells (B31:B38) as a named range (Colours), and to add a person's name to each colour in the legend (in the cell adjacent to the colour).

The following code will create a copy of the "Schedule Color" worksheet for each colour (person), assign the person's name as the name of the new worksheet, and will then remove all colours from its "calendar" except the colour which corresponds to the person after whom the worksheet is named:

Please Login or Register  to view this content.

Hope this helps - please let me know how you get on.

Regards,

Greg M

First of all, to both of you, thank you!

These are two great macros.

I played with both. I found that each one has something the other doesnt and if put together it should work perfectly. I know I didn't specify this originally but after seeing these two separate macros, it makes more sense.

Leith's version copies over only the colors and ignores anything that might be in the cell other then the interior color like text or how the cell is formatted (Merged cells).

Gary's version copies over colors but also copies over everything including formatting and text regardless of interior cell color.

If possible, I would like it to run so that interior cell color, & text caries over. if the merging of multiple cells on some sheets and not others creates an issue then I can do with out it and will just color cells accordingly.

I have reattached Gary's workbook and added a sheet called Sample which shows what I'm thinking about.

Hi again,

The attached workbook should do what you want, but unfortunately the merged cells are a bit of a problem.

I've added just two little extra bits of code:

Please Login or Register  to view this content.

in the "For Each Worksheet" loop, and:

Please Login or Register  to view this content.

in the "For Each ScheduleCell" loop.

At present, all cells other than merged cells are processed, but I'll see if I can find a solution which processes merged cells also.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi again,

Success - I don't think the merged cells are going to be a problem after all!

I've added the following modification to the code, and the text in merged cells is now deleted where appropriate:

Please Login or Register  to view this content.

One question: Should merged cells be unmerged on those worksheets where they are not coloured? If so, let me know and I'll see what I can do.

Regards,

Greg M

Greg,

Spectacular!!!

You said "One question: Should merged cells be unmerged on those worksheets where they are not coloured? If so, let me know and I'll see what I can do."

Preferably, yes, but if it is going to take you more than 5 minutes I can do with out merging.

What you provided is really spot on!

I'll leave this thread open. Just let me know if your going to give it a whirl. If not I'll close this up.

Thank you so much!!!!

Hi again,

Many thanks for your feedback - I'm very pleased to know that I seem to have got it right for you.

It won't take me too long to sort out the unmerging side of things, but it's very late right now, so I hope to be able to send you something tomorrow.

Regards,

Greg M

Hi again,

The attached workbook unmerges merged cells which are "uncoloured" on the new worksheets, and restores the borders of the newly-unmerged cells.

Take a look and let me know if there's anything else you need.

Finally, if you're feeling generous, I could use a few reputation points.

Best regards,

Greg M