I have 'Total Value" in Col A 3 rows below the last value and total values in Col B:F , 3 rows after the last value
I have written code to insert a thick outside border from Total value and 5 columns to the right -see sample data below
However when running my macro nothing happens
It would be appreciated if someone cold assist me
I have written code to insert a thick outside border from Total value and 5 columns to the right -see sample data below
However when running my macro nothing happens
It would be appreciated if someone cold assist me
Thick Outside Border.xlsx | ||||||||
---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | |||
12 | Total Value | 330847 | 181011 | 10055 | 79135 | 60644 | ||
Sheet1 |
Code:
Sub Totals()
Dim c As Range
With Sheet1
Set c = .Columns(1).Find("Total Value", , , 1)
If Not c Is Nothing Then
c.Offset(, 1).Resize(, 5).Value = "=sum(b1:b" & c.Row - 1 & ")"
Else
lr = .Cells(.Rows.Count, 1).End(xlUp).Row
.Cells(lr + 3, 1).Value = "Total Value"
.Cells(lr + 3, 2).Resize(, 5).Value = "=sum(b4:b" & lr & ")"
.Cells(lr + 3, 2).Resize(, 5).NumberFormat = "#,##0.00"
.Cells(lr + 3, 2).Resize(, 5).Font.Bold = True
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeLeft).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeTop).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeBottom).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlEdgeRight).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlInsideVertical).LineStyle = xlContinuous
.Cells(lr + 3, 1).Resize(, 5).Borders(xlInsideHorizontal).LineStyle = xlContinuous
End If
End With
End Sub