VBA, Find 2 cells and use them as the range for basic formating-CodePudding

I have created the following VBA in order to determine a dynamic range for some basic formatting. I want to define 2 cells in a row and apply formatting to the range.

Sub RANGEFORMAT()
'
' RANGEFORMAT Macro
'

'
Dim rng As Range
    Set rng = Worksheets("Sheet1").Rows(3).Find(What:="Total", LookAt:=xlWhole)
Dim rng2 As Range
    Set rng2 = Worksheets("Sheet1").Rows(3).Find(What:="100 - Indirect Labour - Shop", LookAt:=xlWhole)
Range(rng2:rng).Select
With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub

I know the line Range(rng2:rng).Select is incorrect, but I am unsure of what syntax to use to define the ranges in way that can work.

Thanks in advance!

CodePudding user response：

There is no need to Select. Also, it is preferable to test if both Find calls succeeded, before applying formatting.

With Worksheets("Sheet1")
    Dim rng As Range
    Set rng = .Rows(3).Find(What:="Total", LookAt:=xlWhole)

    Dim rng2 As Range
    Set rng2 = .Rows(3).Find(What:="100 - Indirect Labour - Shop", LookAt:=xlWhole)

    ' Check to see if the Find succeeded
    If Not rng Is Nothing And Not rng2 Is Nothing Then
         ApplyTheFormat .Range(rng2, rng)
    End If
End With

....

Private Sub ApplyTheFormat(ByVal rng As Range)
   With rng
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 90
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub