Add borders to certain cells using VBA

[Drew]

I have a schedule file in Excel that I am trying to automatically create
borders on certain cells. The schedule file is made up of 4 worksheets,
First Shift, Second Shift, Third Shift and Unit Totals. There are 3
different Units, each with it's own schedule workbook. The Units are made
up of living units, and they have different numbers of schedules.

You can find a sample copy of the schedules at the following link,
http://www.swvtc.dmhmrsas.virginia.gov/sched/

Now for my question. How can I develop a macro to scan through a certain
row (say 5) and find the value W (for Wednesday) and then create a left
border for that cell and all the cells below it to a certain cell, and then
return to Row 5 and continue on until it encounters W again, and does it
again. The reason for this is to denote the workweek, which runs from
Wednesday to Tuesday.

It doesn't matter to me if I have to hand-code each different schedule,
since this will only be run once every few years.

Thanks,
Drew Laing

 

[Guest]

Try:

Sub gsnu()
Dim r As Range
Set r = ActiveSheet.UsedRange
nlastrow = r.Rows.Count + r.Row - 1
For i = 1 To 255
If Cells(5, i).Value = "W" Then
For j = 5 To nlastrow
Cells(j, i).Borders(xlEdgeLeft).LineStyle = xlContinuous
Next
End If
Next
End Sub

 

[Drew]

That works, with a few changes,

On more question, how can I specify to use .Weight = xlMedium, I tried, but
it kept erroring out.

Sub gsnu()
Dim r As Range
Set r = ActiveSheet.UsedRange
nlastrow = 12
For i = 1 To 255
If Cells(5, i).Value = "W" Then
For j = 5 To nlastrow
Cells(j, i).Borders(xlEdgeLeft).LineStyle = xlContinuous
Next
End If
Next
End Sub

Since there are more than 1 schedules on each worksheet, I needed to limit
the nlastrow to a cell.

Thanks,
Drew Laing

 

[Guest]

For j = 5 To nlastrow
Cells(j, i).Borders(xlEdgeLeft).LineStyle = xlContinuous
Cells(j, i).Borders(xlEdgeLeft).Weight = xlMedium
Next

 

[Drew]

Great!

Thanks a bunch,
Drew

For j = 5 To nlastrow
Cells(j, i).Borders(xlEdgeLeft).LineStyle = xlContinuous
Cells(j, i).Borders(xlEdgeLeft).Weight = xlMedium
Next