Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run-time error (97 Sr-2)

    A while ago, I made a spreadsheet that would change the background color of some cells using some macro code. The code worked fine at the time. Now, I am farther along and am coming across run-time errors with the code. I'm having difficulty figuring out what is triggering the error, so I was wondering what this error means?

    Here is the error message:
    Run-time error '1004':

    No cells were found


    The macro code (with the line that errors in bold) is:
    <pre>Option Explicit

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    If Intersect(Target, Worksheets("Elapsed Time Shift 1").Range("myColorSource")) Is Nothing Then
    Exit Sub
    End If
    Application.EnableEvents = False
    Worksheets("Labor Breakdown").Range("myColorTarget").Interior.ColorI ndex = 0
    For Each rngCell In Worksheets("Labor Breakdown").Range("myColorTarget").SpecialCells(xl CellTypeFormulas, 1)
    rngCell.Interior.ColorIndex = (rngCell.Value Mod 53) + 3
    Next rngCell
    Application.EnableEvents = True
    End Sub</pre>


    My original post is here:
    http://www.wopr.com/cgi-bin/w3t/showflat.p...b=5&o=14&fpart=

    I'm willing to research deeper into what is causing the problem, but I thought there might be an easy fix or an easy way to avoid the error. This error seems to occur mainly when you just start out.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error (97 Sr-2)

    It means no cells containing formulas which yield values were found in .Range("myColorTarget").

    For Each rngCell In Worksheets("Labor Breakdown").Range("myColorTarget").SpecialCells(xl CellTypeFormulas, 1)

    In the WB we last looked at, the range myColorTarget was full of formulas which looked like

    =IF('Elapsed Time Shift 1'!$D5=1,'Elapsed Time Shift 1'!$B5,"")

    Is there a possibility they all evaluate to "", or did you make some other change to this range? If so go back to Legare's post where he used a different construct to evaluate each cell.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Lounger
    Join Date
    Aug 2002
    Location
    MN, USA
    Posts
    49
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run-time error (97 Sr-2)

    Initially all the cells in that range do equal "". I see what you are getting at, and I think it is the problem. Is there something I can do to the code or should I make sure that values are always yielded?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Run-time error (97 Sr-2)

    Change just this part:

    For Each rngCell In Worksheets("Labor Breakdown").Range("myColorTarget")
    If IsNumeric(rngCell.Value) Then rngCell.Interior.ColorIndex = (rngCell.Value Mod 53) + 3
    Next rngCell
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •