Formating range of cells

[srikanth58]

Hi all,

I have a perpetual yearly calender in a 54X7 grid, using excel conditional formating I could mark week ends, public holidays and first day of the months. Now being exhausted of the conditional formatting limit provided by excel I want to know how to change the color of a range of cells, with VBA, having dates falling within turn around period of the establishment where no employee can apply leave. This turn around may be of 15 to 30 days period any where in the year and calendar cells with in this period should have different background color. Turn around period will be entered in separate cells as start date and end date.

I am really new for VBA, I would be very much relieved if the code comes with comment lines so that I understand what I am doing.

Thank you all in advance.
sri

[mdmackillop]

Hi Sri,
Welcome to VBAX
Does this KB Item help?
Regards
MD

[srikanth58]

Hi MD,
Thanks in advance for your helping hand,

I have 2 ranges C13:BF19 (7X56 grid) and C21:BF27,
A1 is the input for the year
A2 Turn around Start DAte B2 is Turn around End Date
A3 Second TA Start B3 is Second Turn Around End Date.

C21 > DATE(A1,1,1)
D21 > C21+1 (formated to 'd')
C22 > BF21+1
Like wise the calendar grid is completed. This is the actual calendar which is hidden.

For displayed calendar in the range C13:BF19,
C12 > LEFT(TEXT(C13,"ddd"),1) upto BF12 (Weekdays list)
C13 > IF(DAY(C21)=1,TEXT(C21,"mmm"),C21).

Also I have a public holiday list from BH13:BH24

Formating of displayed calendar C13:BF19,

For begining of the month, IF(DAY(C21)=1) > RED
For weekends, IF (OR (DAY(c21)=6,DAY(C21)=7) > GREEN
For Public Holidays, COUNTIF(BH13:BH24,C21)>0 > YELLOW

I think I have explained the things I have done so far,

Now I want to high light the turn around period, in the displayed calendar C13:BF19 , falling with in the dates between A1:B1 and A2:B2.

Further is it possible to rotate the month name text to 90 degrees? Because the begining of the month cell value is TEXT and not number.

As I said before, I am quite new to VBA and I do not know how to adopt the code in the link you mentioned to my requirement.

Thanks a lot for sparing your time to read this through.

sri