CP212 : Sample Ranges

Sub EndAndOffset()
    ' Demonstrate End and Offset property
    ' See text page 94
    
    ' Demo of Range selections
    ' Uses "first cell, last cell" notation
    Range("B3", "E6").Interior.Color = vbYellow
    
    MsgBox "See? The cells are yellow.", , "Just a pause"
    
    'Basic Usage
    'Colors the range now matter what size
    Range("B3", Range("B3").End(xlDown).End(xlToRight)).Interior.Color = vbGreen
    
    ' A bit more concise
    ' Use B3 as an "anchor" cell
    With Range("B3")
        Range(.Offset(0, 0), .End(xlDown).End(xlToRight)).Name = "CompanyData"
    End With
    
    ' Format and name the headings
    With Range("B3")
        Range(.Offset(0, 0), .Offset(0, Range("CompanyData").Columns.Count - 1)).Name = "TableHeadings"
    End With
    Range("TableHeadings").Font.Bold = True
    
    ' Format the data differently, 1 row down from B3
    With Range("B3")
        Range(.Offset(1, 0), .End(xlDown).End(xlToRight)).Font.Italic = True
    End With
    
    End Sub
End Sub