Conditional Formatting for Entire Columns

[cmoore24]

I have a calendar from now until April 2017 that has every weekend (Saturday and Sunday) shaded with the 40% - Accent 3. I have a macro that will autofill a range of dates in a different color when someone enters the dates. I want to format the weekend days so that they'll be shaded to look darker, even if they are filled with a different color (for example if a 2 week period is highlighted red, the two weekends that are highlighted red show up a little darker). Can this be done?

[Aardigspook]

Hi,

You can do this with conditional formatting.

Let's assume:
- Column A contains your list of dates
- start date and end date of the date range of interest are in C1 and C2

Select Column A, click Conditional Formatting, click 'New Rule' and select 'Use a formula to determine which cells to format'.
Put in this formula:
=OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7)
choose the colour you want ALL weekends to be shaded.

Now, with Column A still selected, repeat the above but with this formula:
=AND(A1>$C$1,A1<=$C$2)
choose the colour you want the selected date range to be.

Still with Column A selected, repeat again with this formula:
=AND(A1>$C$1,A1<=$C$2,OR(WEEKDAY(A1)=1,WEEKDAY(A1)=7))
choose the colour you want the weekends in the selected date range to be.

The attached file shows this working.
CF highlight weekends plus range of dates.xlsx

I'm not very good with VBA, so have no idea how to translate that into a macro, but I'm sure it's possible, if you need it to be done in VBA.

ps on my computer, weekdays 1 and 7 are Sunday and Saturday - depending on your settings, you may need to amend these days.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[cmoore24]

Thank you. That works out conditionally but my macro ends up superseding it. I'll just have to do a little trial and error and see if I can figure it out. I appreciate your help.

[Aardigspook]

Ok, I took this opportunity to learn a bit of VBA. I'm sure the code could be tighter (i.e. shorter and more efficient), but this seems to work.
Again, the dates are in Column A, with the start and end date of the range to be selected in C1 and C2.

Please Login or Register  to view this content.

Here's a file with it in - I've run it on the 'CF done' sheet, but not on the 'CF not done' sheet (yes, I'm very imaginative with my sheet names ).
CF weekends with macro.xls
The file is in 2003 (.xls) format, as that's what I've got at work. You should be able to re-save it as a macro-enabled 2007/10/13 file (.xlsm) without anything changing.

Hopefully you can figure out how to incorporate the above into your already-existing macros (which I'm sure are a lot more complicated than this!).

Good luck.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' on your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.