VB code for autofill

[Kobus]

This will be easy for a VB coder: I have a row range E1V1 with concurrent
dates starting Aug 2010 to Nov 2010. I want the rows (lets say 20) below
weekends to be autofilled (Result: Weekends will be visible as columns)
without being affected by subsequent conditional formatting or VB code. The
subsequent code or conditional formatting must autofill between date ranges
obtained from corresponding cells in columns C&D.

 

[OssieMac]

Lets see if I fully understand your question.
You have a row of dates (1st Aug 2010 to 30th Nov 2010) in cells E1V1.
You have a start date in column C
You have an end date in column D
You want all weekend dates filled in for all the columns.
You want the weekday dates filled in for the period between the start and end.

If above is correct then it can be done with a fomula without using VBA.

Assume that C2 contains a start date and D2 an end date.
Enter the following formula in E2.

=IF(AND(E$1>=$C2,E$1<=$D2,WEEKDAY(E$1,2)<6),E$1,IF(WEEKDAY(E$1,2)>5,E$1,""))

Note: Although the formula may appear as 2 lines in the post, it is actually
one line so you will have to fix that up when you copy it into the formular
bar.

You should be able to copy the formula across all columns and down as far as
you want.

Entering or changing values in columns C and D should produce the results
you desire.

Note: If both C and D are blank or only C contains a date, only the weekends
will be populated. If only column D contains a date and C is blank, then all
columns will be populated.

 

[Kobus]

I used the wrong term. I need blank cells to be colour filled according to
the criteria. I could use the formula and then apply conditional formatting
afterward with your solution. (I am currently working with the OR function
and think it may also work) The effect should be a bar chart effect

Lets see if I fully understand your question.
You have a row of dates (1st Aug 2010 to 30th Nov 2010) in cells E1V1. (YES)
You have a start date in column C (YES)
You have an end date in column D (YES)
You want all weekend dates filled in for all the columns. (IF DATE IN E1 =
WEEKEND THEN COLOUR FILL)
You want the weekday dates filled in for the period between the start and
end. (COLOUR FILLED)

 

[OssieMac]

Do you mean that you do not want to see the dates; just the fill color?

If above assumption is correct then don't use my previous suggestion and
apply conditional format as follows.

Select the entire range to be conditionally formatted. (E2V? where ? is
max row.)
Select conditional format.
Select formula.
Enter the following formula for week days fill.
=AND(E$1>=$C2,E$1<=$D2,WEEKDAY(E$1,2)<6)
Click Format button and set fill color.
Add a rule.
Enter the following formula for weekend fill.
=WEEKDAY(E$1,2)>5
Click Format button and set fill color.

Above should work irrespective of whether you use my previous suggestion to
display the dates or if you don't want the dates displayed and you just want
to see the colors.

Don't know if you are aware of this but a tip for entering Conditional
Format. Select the entire range to be formatted and enter the formula as if
you are formatting the first cell in the range only. However, you do have to
think about what addresses need to have absolute addressing (with the $
signs) if the formula is to apply to the rest of the selection. Excel then
automatically applies the Conditional Format to the entire selected range