Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code for autofill
This will be easy for a VB coder: I have a row range E1:DV1 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code for autofill
Lets see if I fully understand your question.
You have a row of dates (1st Aug 2010 to 30th Nov 2010) in cells E1:DV1. 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. -- Regards, OssieMac |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code for autofill
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 E1:DV1. (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" wrote: Lets see if I fully understand your question. You have a row of dates (1st Aug 2010 to 30th Nov 2010) in cells E1:DV1. 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. -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code for autofill
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. (E2:DV? 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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill data based on an assigned code/prefix | Excel Worksheet Functions | |||
How to Autofill HTML code into Excell spreadsheet | Excel Programming | |||
Autofill cells in 1 column :code ?? | Excel Programming | |||
AutoFill Formula with code | Excel Programming | |||
VBA code to Autofill one cell to many rows below where row count will change | Excel Programming |