Place Borders around specific area and add formula

[Mister H]

Hi:

I am trying to peice together a code but am having some difficulties due to my VBA Ignorance

What I want to do is that if there is data in cell A of any given row then place a border around A:I of that row.

I then need to go to the cell Containing the word "Total" and OFFSET 1 cell to the right and sum up the $$$ in cell from Row 2 down to where the formula is being inserted.

Sub Borders()

 
    Dim myLastRow As Long
    Dim i As Long
        'Dim myFormula As String
    
i = Range("A1").End(xlDown).Row
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'   Select A to I and palce borders around the selection (NOT WORKING)
    Range("A:I" & i).Select
    
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
        End With
 
'Insert Formula to ADD up column H
'RECORDED with Macro Recorder
    Cells.Find(What:="Total", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Range("H9").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-7]C:R[-1]C)"        
        
End Sub

Quite posdsibly I am going about this the wrong way.

Any guidance or links would be GREATLY Appreciated.

THANKS,
Mark

 

[Mister H]

Hi:

I ACTUALLY figured it out

This may not be pretty but it seems to be working:

Sub AddBordersAndFormula()

    Dim myLastRow As Long
    Dim i As Long
i = Range("A1").End(xlDown).Row
    
'   Find last row with data in column A
    myLastRow = Cells(Rows.Count, "A").End(xlUp).Row
'Select Range
    Range("A1" & ":I" & i).Select
'Apply Borders
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Weight = xlHairline
        .ColorIndex = xlAutomatic
    End With
    Range("A1").Select
    
'Add formula to Sum up Column H
    Lastrow = Range("H65536").End(xlUp).Offset(1, 0).Row
    Range("H" & Lastrow) = "=Sum(H2:H" & Lastrow - 1 & ")"
        
End Sub

THANKS,
Mark