Referencing Column Range

[Kel09]

Hi, can anyone help me with this bit of code shown below (The code below forms part of a larger code) What it's suppose to do is add color to columns A-H. It runs but it will only add color to column A. Where have I gone wrong?
Note: S and L are = Dim L As Range / Dim S As Range

Many thanks


Code:
With ActiveSheet.Range("A13:H50")
Set S = Cells.Find("Small", MatchCase:=True, lookat:=xlWhole, LookIn:=xlValues)
If Not S Is Nothing Then
ActiveSheet.Range(Cells(13, 1), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
End If
Set L = Cells.Find("Large", MatchCase:=True, lookat:=xlWhole, LookIn:=xlValues)
If Not L Is Nothing Then
ActiveSheet.Range(Cells(13, 1), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
End If
End With


The only way I can get it to work is if I write the code in the following way:
With ActiveSheet.Range("A13:H50")
Set S = Cells.Find("Small", MatchCase:=True, lookat:=xlWhole, LookIn:=xlValues)
If Not S Is Nothing Then
ActiveSheet.Range(Cells(13, 1), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 2), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 3), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 4), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 5), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 6), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 7), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3
ActiveSheet.Range(Cells(13, 8), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 3

End If
Set L = Cells.Find("Large", MatchCase:=True, lookat:=xlWhole, LookIn:=xlValues)
If Not L Is Nothing Then
ActiveSheet.Range(Cells(13, 1), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 2), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 3), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 4), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 5), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 6), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 7), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
ActiveSheet.Range(Cells(13, 8), Cells(13, 8)).End(xlDown).Interior.ColorIndex = 4
End If
End With

 

[AlphaFrog]

Maybe try something like this...

    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    LastRow = Cells.Find("*", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row
    [color=darkblue]With[/color] ActiveSheet.Range("A13:H50")
        [color=darkblue]If[/color] [color=darkblue]Not[/color] .Cells.Find("Small") [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 3
        [color=darkblue]ElseIf[/color] [color=darkblue]Not[/color] .Cells.Find("Large") [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 4
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]

 

[Kel09]

Maybe try something like this...

    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    LastRow = Cells.Find("*", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row
    [COLOR=darkblue]With[/COLOR] ActiveSheet.Range("A13:H50")
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] .Cells.Find("Small") [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 3
        [COLOR=darkblue]ElseIf[/COLOR] [COLOR=darkblue]Not[/COLOR] .Cells.Find("Large") [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
            Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 4
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

Thank you, so much!
I had to tweak it a little bit. Ended up with this:

Dim LastRow As Long
LastRow = Cells.Find("*", , LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Row
With ActiveSheet.Range("A13:H50")
If Not .Cells.Find("Small") Is Nothing Then
Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 3
If Not .Cells.Find("Large") Is Nothing Then
Cells(LastRow, 1).Resize(, 8).Interior.ColorIndex = 4
End If
End If
End With