-
copy this macro in Excel spreadsheet..U can edit this by opening VB editor
Sub dateFunc()
'
' dateFunc Macro
' Macro recorded 6/9/00 by Neelesh
'
currDt = Date 'day when u want to run macro
newdt = currDt
currmon = Month(currDt) 'stores curr month
For num = 2 To 32 '2 assuming ur cell starts as location 2, 32 bcos max possible days=31
cellnum = Chr(32) & "B" & LTrim(Str(num)) & Chr(32)
Range(cellnum).Select
ActiveCell.FormulaR1C1 = newdt
newdt = newdt - 1 'day b4
If Month(newdt) <> currmon Then Exit Sub
Next num
End Sub
-
it works great. how do i format the date to be jun-01-00???? that would be really helpful.
Also if i would like the dates to be shaded -- borderfill, could i set up a loop until the date is finished populating and get those same fields shaded?????
thank you for all the help!!!
-
Replace
statement ActiveCell.FormulaR1C1 = newdt
with
ActiveCell.FormulaR1C1 = Format(newdt, "mmm-dd-yy")
Abt shading...
I dont know how 2 do that fancy stuff...here is what u can do....Goto Record Macro...Give some name 2 it...Go ahead & do the bordering/coloring...Then stop the macro...look at the code generated...
Good Luk...
-
thank you that worked like a charm!!!
