Create a border around a range for specific criteria

[nelsonlauo]

Hi all,

I have a range of records and the number of rows down will grow or shrink dynamically depending on how much data has been inputted, so there is no set endpoint. The columns is also the same.

Is there a way to generate an outline border around the entire range of cells include next rows used based on SA_START_DATE (at column O, AA, AM, and so on)?

Current:
25961

Expected results:
25962

Have attached sample Excel with dummy data.
25963

Your ideas/ suggestions are welcome. Thanks for your help!

[nelsonlauo]

After the trial-and-error pays off, I'm glad to share the following code which can solve this question with you all.


If Worksheets(WS).Cells(i, Col + 6).value = Worksheets(WS).Cells(i - 1, Col + 6).value Then
Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i, Col + 10).Address()).BorderAround LineStyle:=xlContinuous, Color:=vbCyan, Weight:=xlThick
Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i, Col + 10).Address()).Borders(xlEdgeTop).LineStyle = xlContinuous
Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i, Col + 10).Address()).Borders(xlEdgeTop).Color = vbBlack
Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i, Col + 10).Address()).Borders(xlEdgeTop).Weight = xlThin
' Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i + 1, Col + 10).Address()).Borders(xlInsideHorizontal).LineStyle = xlNone
Else
Worksheets(WS).Range(Cells(i, Col).Address(), Cells(i, Col + 10).Address()).BorderAround LineStyle:=xlContinuous, Color:=vbCyan, Weight:=xlThick
End If

[p45cal]

That snippet could also be:
With Worksheets(WS).Cells(i, Col).Resize(, 11)
.BorderAround LineStyle:=xlContinuous, Color:=vbCyan, Weight:=xlThick
If .Cells(7).Value = .Cells(7).Offset(-1).Value Then
With .Borders(xlEdgeTop)
.Color = vbBlack
.Weight = xlThin
End With
End If
End With

[nelsonlauo]

Thanks! More condensed


That snippet could also be:
With Worksheets(WS).Cells(i, Col).Resize(, 11)
.BorderAround LineStyle:=xlContinuous, Color:=vbCyan, Weight:=xlThick
If .Cells(7).Value = .Cells(7).Offset(-1).Value Then
With .Borders(xlEdgeTop)
.Color = vbBlack
.Weight = xlThin
End With
End If
End With