Formatting to last row

[ExcelHobbit]

I want to highlight cell b37 and until the last row (which might change) in order to format that range of data

this is what i have so far, whereby i have declared at the very start of the code the following:
Dim Lastrow as Long

originally i had it as range("B37:B150").Select, but i had to change this as the last row will change

Range("B37:B & Rows.Count").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlMedium
    End With

 

[Fluff]

How about

Sub ExcelHobbit()
   With Range("B37", Range("B" & Rows.Count).End(xlUp))
      .borders(xlDiagonalDown).LineStyle = xlNone
      .borders(xlDiagonalUp).LineStyle = xlNone
      With .borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = xlAutomatic
          .TintAndShade = 0
          .Weight = xlMedium
      End With
   End With
End Sub

 

[ExcelHobbit]

thanks for responding. when i put that code in, it seems to only do cell B37, and not highlight all cells below it up until the last row...

 

[Fluff]

That suggest that there is nothing in column B below B37, is that the case?

 

[ExcelHobbit]

there is data in B37, so can't figure out why it isn't working

 

[Fluff]

Is there anything below B37?

 

[ExcelHobbit]

yes there is. So i want B37 highlighted and down until the last cell of data, perhaps using until the last row of data found in column A

 

[Fluff]

Ok, try

Sub ExcelHobbit()
   With Range("B37:B" & Range("A" & Rows.Count).End(xlUp).Row)
      .borders(xlDiagonalDown).LineStyle = xlNone
      .borders(xlDiagonalUp).LineStyle = xlNone
      With .borders(xlEdgeLeft)
          .LineStyle = xlContinuous
          .ColorIndex = xlAutomatic
          .TintAndShade = 0
          .Weight = xlMedium
      End With
   End With
End Sub