Macro to add coloured Border on column based on recurring dates

[regan_2000]

Hi, Thank you for all your fantastic help and tips.

I want to be able to add a macro to an excel spreadsheet that adds a border on one side of a column that recurs every 14 days

From the image attached, I would like:
Think Blue line on the left hand side of the column
Recurring every 14 days (so every 14 columns) (it is between Wed and Thurs, every second week to display the payday)
Runs from Rows 1 to 6

Hope this is not too hard as it is a manual process for me currently

Thanks in advance

 

[Sam_Sep]

Hi,
Try this

Sub Macro1()

For i = 1 To 100

If Weekday(ActiveSheet.Cells(1, i)) = 4 Then

ActiveSheet.Cells(1, i).Borders(xlDiagonalDown).LineStyle = xlNone
ActiveSheet.Cells(1, i).Borders(xlDiagonalUp).LineStyle = xlNone
ActiveSheet.Cells(1, i).Borders(xlEdgeLeft).LineStyle = xlNone
ActiveSheet.Cells(1, i).Borders(xlEdgeTop).LineStyle = xlNone
ActiveSheet.Cells(1, i).Borders(xlEdgeBottom).LineStyle = xlNone
With ActiveSheet.Cells(1, i).Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = -0.249946592608417
.Weight = xlMedium
End With

End If
Next i

End Sub



formatcellwithdayofweek.xlsm

 

[NoSparks]

Select the first Thursday in your row of dates that needs the left boarder then run this macro

Sub BoarderEvery14thColumn()
    
    Dim i As Long, rw As Long
    Dim startCol As Long, lastCol As Long
   
rw = Selection.Row
startCol = Selection.Column
lastCol = Cells(rw, Columns.Count).End(xlToLeft).Column

For i = startCol To lastCol Step 14
    With Cells(1, i).Resize(6).Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = vbBlue
        .TintAndShade = 0
        .Weight = xlThick
    End With
Next i

End Sub