Macro for conditional formatting bottom border

Hello,

I wonder if it is possible to create a macro that would detect end page (jumping from page 1 to page 2 for instance) and add a bottom border at the last row of the page.

I have attached a sample where I highlight in green the end of the page and added manually the bottom border (I did it only for the first end pages).

I would really appreciate if you can help me with that?

Many Thanks,
Graig

hi Graig, sort of that

Sub test()
    
Dim rng As Range, sh As Worksheet, breaks_count as long
    
On Error Resume Next
    
Set sh = ActiveSheet
    
breaks_count = sh.HPageBreaks.Count

For i = 1 To breaks_count
    If rng Is Nothing Then Set rng = sh.Cells(sh.HPageBreaks(i).Location.Row - 1, 1) Else Set rng = Union(sh.Cells(sh.HPageBreaks(i).Location.Row - 1, 1), rng)
Next
    
If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).Weight = xlThin
    
End Sub

Hello watersev,

It kind of work but I need to have the bottom line for the columns B:F. do you think it is possible?

Thanks,
Graig

Sub test()
    
Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long
 
Set sh = ActiveSheet

sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate

breaks_count = sh.HPageBreaks.Count

For i = 1 To breaks_count
    Debug.Print sh.HPageBreaks(i).Location.Address
    If rng Is Nothing Then Set rng = sh.Range(sh.HPageBreaks(i).Location.Address).Offset(-1, 1).Resize(, 7) Else Set rng = Union(sh.Range(sh.HPageBreaks(i).Location.Address).Offset(-1, 1).Resize(, 7), rng)
Next
    
If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
    
End Sub

I only miss the B column. Otherwise it works nicely! I tried to modify your macro but I got lost it doesn't work for me :-S can you please help me?

do you mean this?

Sub test()
    
Dim rng As Range, sh As Worksheet, breaks_count As Long, i As Long
 
Set sh = ActiveSheet

sh.Range("a" & sh.ListObjects(1).ListRows.Count).Activate

breaks_count = sh.HPageBreaks.Count

For i = 1 To breaks_count
    If rng Is Nothing Then Set rng = sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7) Else Set rng = Union(sh.Range("B" & sh.HPageBreaks(i).Location.Row - 1).Resize(, 7), rng)
Next
    
If Not rng Is Nothing Then rng.Borders(xlEdgeBottom).LineStyle = xlContinuous
    
End Sub

if to run the code in your posted file the line goes from B to H