'Run-time error 1004: Method 'Range' of object '_Global' failed' only with xls files, but not with txt or xlsx files

Question

I have this piece of code which searches for a phrase, sets the range as the cells to the right of the cell containing the phrase all the way to the last data column and formats the cells with conditional formatting. This code works fine with both txt files and xlsx files, but the phrase

Set rngHeaderAs

gets run-time error 1004: Method 'Range' of object '_Global' failed when I run the code on an xls files. The phrase in the Find section definitely exists, and if I save the very same xls file as an xlsx file, the code runs perfectly.

The code:

Sub Color_labreport_horizontal()

Cells.Replace What:="n,d.", Replacement:="n.d.", lookat:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False

On Error Resume Next
'As

Set rngHeaderAs = Range("A1:ZZ200").Find("As*Arsen*", lookat:=xlPart) 'This string generates the error
Set rngAs = Range(rngHeaderAs, rngHeaderAs.End(xlToRight))
AsAddress = rngHeaderAs.Address(False, False)

Dim Ul1As As Double
Ul1As = 8
Dim Ul2As As Double
Ul2As = 20
Dim Ul3As As Double
Ul3As = 50
Dim Ul4As As Double
Ul4As = 600
Dim Ul5As As Double
Ul5As = 1000


With ActiveSheet

    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & "<" & Ul1As & ")"
        .FormatConditions(1).Interior.ColorIndex = 33
        .FormatConditions(1).Borders.LineStyle = xlContinuous
        .FormatConditions(1).Borders.Weight = xlThin

    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul1As & ";" & AsAddress & "<" & Ul2As & ")"
        .FormatConditions(2).Interior.ColorIndex = 4
        .FormatConditions(2).Borders.LineStyle = xlContinuous
        .FormatConditions(2).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul2As & ";" & AsAddress & "<" & Ul3As & ")"
        .FormatConditions(3).Interior.ColorIndex = 6
        .FormatConditions(3).Borders.LineStyle = xlContinuous
        .FormatConditions(3).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul3As & ";" & AsAddress & "<" & Ul4As & ")"
        .FormatConditions(4).Interior.ColorIndex = 45
        .FormatConditions(4).Borders.LineStyle = xlContinuous
        .FormatConditions(4).Borders.Weight = xlThin

    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul4As & ";" & AsAddress & "<" & Ul5As & ")"
        .FormatConditions(5).Borders.LineStyle = xlContinuous
        .FormatConditions(5).Borders.Weight = xlThin
        .FormatConditions(5).Interior.ColorIndex = 3
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=AND(ISNUMBER(" & AsAddress & ");" & AsAddress & ">=" & Ul5As & ")"
        .FormatConditions(6).Interior.ColorIndex = 7
        .FormatConditions(6).Borders.LineStyle = xlContinuous
        .FormatConditions(6).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=LEFT(" & AsAddress & ";1)=""<"""
        .FormatConditions(7).Interior.ColorIndex = 33
        .FormatConditions(7).Borders.LineStyle = xlContinuous
        .FormatConditions(7).Borders.Weight = xlThin
    End With
    With rngAs
        .FormatConditions.Add xlExpression, Formula1:="=(" & AsAddress & ") = ""n.d."""
        .FormatConditions(8).Interior.ColorIndex = 33
        .FormatConditions(8).Borders.LineStyle = xlContinuous
        .FormatConditions(8).Borders.Weight = xlThin
    End With
End With

Any idea why this happens only when I run it on xls files?

Answer 1

Ron Rosenfelt gave me the answer in the comment. ZZ200 was outside the allowable range, so instead of defining the range as A1:ZZ200 (or some other hardcoded range), I used CurrentRegion:

 Set rngHeaderAs = Range("A1").CurrentRegion.Find("As*Arsen*", lookat:=xlPart)

to make sure that I cover the range I want without exceeding allowable range. Now the code runs perfectly also in Excel 97-compatible files.