Drawing borders with Macro

[Drew]

I am reworking an existing Excel schedule to reflect some administrative
changes. I have done all of the major work, but I made a mistake while
doing it. The schedule has columns for each day, then rows for each
employee. If the employee works, there is a 1 in the cell for the
corresponding day, if not there is an X. Each work week is divided by a
bold border, so that it is easy for the timekeepers to see when the work
week ended. When I initially sectioned out the work weeks, I made the bold
border between Saturday and Sunday, but alas, the work week runs from Wed to
Tue. Now I need to change all of this, and I thought maybe a macro could
help out.

How can I build a macro to draw borders between T and W?

Thanks,
Drew

 

[Executor]

Hi Drew,

First a small warning:
A sheet only has 256 columns, so you would not be able to record a full
year.

I have cooked this macro to reset the borders for each week:

Sub NewEndOfWeek()
'
' NewEndOfWeek Macro
' This macro is created by Executor on 19 jan 2005
'

'
Dim lngLastRow As Long
Dim lngLastCol As Long
Dim lngLoopCOls As Long
Dim strLast As String

lngLastRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
lngLastCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column

For lngLoopCOls = 2 To lngLastCol
If IsDate(Cells(1, lngLoopCOls)) Then
If Weekday(Cells(1, lngLoopCOls)) = vbSunday Then
Range(Cells(1, lngLoopCOls), Cells(lngLastRow,
lngLoopCOls)).Select
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
ElseIf Weekday(Cells(1, lngLoopCOls)) = vbWednesday Then
Range(Cells(1, lngLoopCOls), Cells(lngLastRow,
lngLoopCOls)).Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
End If
Next
Range("A1").Select
End Sub


Hoop this helps


Executor