To be foolproof I would use something like this... Run Test

Sub test()
Intersect(Union(Range("B2"), Range("B4", _
Cells(LastCell.Row, "B"))).EntireRow, _
Range("B:B, D:D, F:F, H:H").EntireColumn).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub


Public Function LastCell(Optional ByVal wks As Worksheet, _
Optional ByVal blnConstantsOnly As Boolean) As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngLookIn As Long

If blnConstantsOnly = True Then
lngLookIn = xlValues
Else
lngLookIn = xlFormulas
End If

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastColumn = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)

End Function
--
HTH...

Jim Thomlinson


"KennyD" wrote:

Need help changing the range on this range so that it will go to the last
row. For example, instead of going from B2,B4:B18, I need it to go from
B2,B4:"the last row that has any information in it". Same with column D,
Column F and Column H. So I imagine something like B2,B4:B&LastRow. But
don't know how to make that happen. Any help would be greatly appreciated.

Range("B2,B4:B18,D2,D4:D18,F2,F4:F18,H2,H4:H18").S elect
Range("H4").Activate
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.PatternTintAndShade = 0
End With
--
Nothing in life is ever easy - just get used to that fact.