Find End of Range,

[ryguy7272]

I am hoping to find a way of finding the end of a used range (different on
each sheet), move over five columns, and color the entire range, in column X,
from cell X6 to this cell lower boundary of the used range.

I can find the end of the used range with this line of code:
Cells(Rows.Count, "S").End(xlUp).Select

I need to do this for several spreadsheets in a workbooks that I just
created; I am using a For...Next Loop and this part is fine...just need to
get a handle on this colored range thing.

 

[Wigi]

Hi

Something like this:

Range("X6:X" & Cells(Rows.Count, "S").End(xlUp).Row).Interior.Colorindex = 3
'some number for color

 

[Jim Thomlinson]

Here is a function that finds the last cell on a spreadsheet.

'***********************************
Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function
'***********************************

so you could use code something like this

sub ColourStuff
dim wks as worksheet
for each wks in worksheets
with wks
.range(.range("A1"), lastcell(wks).offset(0,5)).interior.colorindex = 34
end with
next wks
end sub

 

[ryguy7272]

This is what I came up with, and it worked quite well, but what an eyesore!!

Dim StartCell1, EndCell1, FinalCell1 As Range
Dim StartCell2, EndCell2, FinalCell2 As Range

Set StartCell1 = Range("X6")
Set EndCell1 = Cells(Rows.Count, "S").End(xlUp)
Set FinalCell1 = EndCell1.Offset(0, 5)
Range(StartCell1, FinalCell1).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With

Set StartCell2 = Range("Z6")
Set EndCell2 = Cells(Rows.Count, "S").End(xlUp)
Set FinalCell2 = EndCell2.Offset(0, 7)
Range(StartCell2, FinalCell2).Select
With Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With


I'm going to go with Wigi's code. Thanks for the macro Jim; that works well
too.


Regards,
Ryan---