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:
2020-02-11_102605.jpg

Expected results:
2020-02-11_102721.jpg

Have attached sample Excel with dummy data.
macro_SA_20200211.xls

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