problem copy paste cells with conditional formatting from 1 sheet to another

**vayana** · 12-28-2013, 10:42 AM

Hello,

I'm trying to copy/paste a sheet with a calendar and some cells that highlight dates on that calendar to another sheet.
The CF formula that highlights these cells is:

Formula:

Please Login or Register  to view this content.

cf2.jpg

If I copy that to the other sheet in a different row, then the highlighting doesn't work anymore so I altered the formula to:

Formula:

Please Login or Register  to view this content.

Then the first cells with start and end date are highlighted ok within a single month, but it doesn't highlight a date that goes from one month to the next.
cf2.jpg

I need multiple of these calendars with date-highlighting in 1 sheet, but I'm not sure what to do to make that work.

Any help would be greatly appreciated.

Thanks,

Michael.

**Winon** · 12-29-2013, 05:32 AM

Hello vayana,

It would be far better if you uploaded a sample Workbook, instead of some pictures.

**vayana** · 12-29-2013, 05:53 AM

Ok, here you go

The old formula used reference names for each cell in the formula, which made it too much work to change the name each time I wanted to copy/paste it in the same sheet.

The alternative used a grouped reference name for the column which is much faster to work with.

The new formula uses absolute cell-references which is also too much work to change each time I want to paste it down 100+ times.

I've now dropped most CF and kept only 1 CF for highlighting date-ranges and 1 for marking dates with a red outline. This way I only need to rename 3 ranges instead of 9 each time I want to copy paste. I have not found another formula or way to be able to copy/paste these calendars without either a reference-name or an absolute range in the formula. Making the ranges relative breaks the way I want the CF to work.

The goal is to get a sheet with 100+ of these calendars where I can set start and end dates which highlight each individual calendar.

Thanks,

Michael.