Dynamic border formatting based on dates. Use VBA?

[Whizzle]

Hi,

I need to insert borders around blocks of months in a given year, to differentiate the months in one year from the months in another.

The problem I have is that in my model, the dates frequently change.

Essentially, I need some VBA coding which can read the date in a given range of cells and insert a border around the block of cells which correspond to a particular year.

The result will be that there is one big/external border around all the cells in 2012, then another external border around the cells in 2013.

Please note, in the first year, the first month may not necessarily be January, so I cannot assumed the first year has 12 months (and therefore 12 columns) in it.

Any help is really appreciated!

Whizzle

 

[GS]

Geez.., wouldn't it be easier to just use a larger value for the
RowHeight in the first row of each year?

The following procedure assumes dates are in A:A...

Sub SpaceYears()
Dim lYr&, n&, vData
vData = ActiveSheet.UsedRange
For n = LBound(vData) + 1 To UBound(vData)
If Year(vData(n, 1)) <> lYr Then
lYr = Year(vData(n, 1))
With Rows(n): .RowHeight = .RowHeight * 2: End With
End If
Next 'n
End Sub

...which assumes headings in 1:1. (This also work with Date/Timestamp)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion