PDA

View Full Version : [SOLVED] Create a border around a range for specific criteria



nelsonlauo
02-10-2020, 07:37 PM
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
02-11-2020, 02:43 AM
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
02-11-2020, 04:48 AM
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
02-12-2020, 06:24 PM
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