Borders with VBA

[RichM]

Folks, I am in need of your assistance once again. I am working on a reporting format that requires cells with data to have (thin box) borders around it. I have tried various routes that work if the range is known. However, the amount of data various. One week it may be 100 rows, the next 168 or even 24. Is there a VBA script that can handle this?

Thank you for your assistance.

 

[Richie(UK)]

Hi RichM,

Have you tried using Named Ranges?

HTH

 

[Andrew Poulsom]

If your data always starts in the same place and is contiguous it's easy:

Range("A1").CurrentRegion.Select

selects down and across until a blank cell is reached.

 

[RichM]

Thank you both for your response.
Richie - I am not too sure how to create a named range for an unknown.
Andrew - I will try this. What can I do for blank cells. They don't need borders.

Thank you again.
Rich

 

[Andrew Poulsom]

Once you have your range you can loop around it like this:

Sub Test()
   Dim Rng As Range
   Dim c As Range
   Set Rng = Activecell.CurrentRegion
   For Each c in Rng
'     Test if cell is blank
      If c.Value <> "" Then
'     *** Do your borders stuff ***
      End If
   Next c
End Sub

 

[Joe Was]

This may work, without seeing your sheet?
Test it on a copy.
The code go's in the Sheet Module for the sheet you want it to work on. JSW

Sub myOutline(CurrRange As Range)
If Cell.Value < 0 Then
With Cell.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Cell.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Next Cell
End Sub