Selecting a Range

[Karen]

Hello all,
I am trying to use the following code (courtesy of Tom Ogilvy) to
colour the range from B26 to the last occupied cell without success.
You will note that I have named the last occupied cell 'Lastcell'
thinking this will help with selection and delete it at the end as I
need to use it with different sheets in the same workbook.
Where am I going wrong?


Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
ActiveWorkbook.Names.Add Name:="LastCell"
Range("b26:LastCell").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWorkbook.Names("lastcell").Delete
End Sub

TIA

 

[Tom Ogilvy]

Range("b26:LastCell").Select

should be

Range(Range("b26") , Range("LastCell")).Select

 

[Karen]

Tom
Thank you for your reply.
I tried it out but the colouring applied to only the last cell instead
of the Range.

Karen

Range("b26:LastCell").Select

should be

Range(Range("b26") , Range("LastCell")).Select

--
Regards,
Tom Ogilvy

Hello all,
I am trying to use the following code (courtesy of Tom Ogilvy) to
colour the range from B26 to the last occupied cell without success.
You will note that I have named the last occupied cell 'Lastcell'
thinking this will help with selection and delete it at the end as I
need to use it with different sheets in the same workbook.
Where am I going wrong?


Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
ActiveWorkbook.Names.Add Name:="LastCell"
Range("b26:LastCell").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWorkbook.Names("lastcell").Delete
End Sub

TIA

 

[Tom Ogilvy]

Try it this way:

Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
With Range(Range("b26"), _
Cells(RealLastRow, RealLastColumn)).Interior
.ColorIndex = 6
.Pattern = xlSolid
End With

End Sub

--
Regards,
Tom Ogilvy

Tom
Thank you for your reply.
I tried it out but the colouring applied to only the last cell instead
of the Range.

Karen







"Tom Ogilvy" <[email protected]> wrote in message

Range("b26:LastCell").Select

should be

Range(Range("b26") , Range("LastCell")).Select

--
Regards,
Tom Ogilvy

Hello all,
I am trying to use the following code (courtesy of Tom Ogilvy) to
colour the range from B26 to the last occupied cell without success.
You will note that I have named the last occupied cell 'Lastcell'
thinking this will help with selection and delete it at the end as I
need to use it with different sheets in the same workbook.
Where am I going wrong?


Sub GetRealLastCell()
Dim RealLastRow As Long
Dim RealLastColumn As Long
Range("A1").Select
On Error Resume Next
RealLastRow = _
Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
RealLastColumn = _
Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
ActiveWorkbook.Names.Add Name:="LastCell"
Range("b26:LastCell").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
ActiveWorkbook.Names("lastcell").Delete
End Sub

TIA

 

[Karen]

Tom
Thank you very much.
Perfect!

Karen