+ Reply to Thread
Results 1 to 4 of 4

Debug problem when highlighting?

Hybrid View

  1. #1
    Registered User
    Join Date
    05-11-2007
    Location
    A hole in the US.
    Posts
    29

    Debug problem when highlighting?

    Hello, I have a problem with VB giving a "runtime error '13' Mismatch". It only happens when I highlight more than one cell. Here is my code. I will mark on it where the debug takes me too, with a **.

    
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("C10") > 0 And Range("J3") = ("") Then Range("J3") = Now Else J3 = ("")
    If Range("C10") < 1 And Range("J3") > 1 Then Range("J3") = ("")
    End Sub
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim test As Variant
    
    For Each rng In Target
          If Not Application.Intersect(rng, Range("c10:c110")) Is Nothing Then
       **    If Target > 1 Then
                If IsEmpty(Cells(Target.Row, "e")) Then
                   Application.EnableEvents = False
                   Cells(Target.Row, "e").Value = Now()
                End If
             End If
          End If
       Next rng
       Application.EnableEvents = True
       Set rng = Nothing
    End Sub
    
    Private Sub Worksheet_Calculate()
       Dim rng As Range
       ActiveSheet.Protect userinterfaceonly:=True
       For Each rng In Range("c10:c110")
          If rng.Value > 1 Then
             If IsEmpty(Cells(rng.Row, "e")) Then
                Cells(rng.Row, "e").Value = Now()
             End If
          End If
       Next rng
    End Sub
    Thanks for any help that can be given.

  2. #2
    mpeplow
    Guest
    Maybe try changing
    If Target > 1
    to
    If Val(Target) > 1
    or
    If Len(Target) > 1
    What is in your cells? What is the Value of Target when the code breaks? Is it a number? Is it text?

  3. #3
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Try changing Target to rng in the for loop

    For Each rng In Target
          If Not Application.Intersect(rng, Range("c10:c110")) Is Nothing Then
       **    If rng> 1 Then
                If IsEmpty(Cells(rng.Row, "e")) Then
                   Application.EnableEvents = False
                   Cells(rng.Row, "e").Value = Now()
                End If
             End If
          End If
       Next rng
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  4. #4
    Registered User
    Join Date
    05-11-2007
    Location
    A hole in the US.
    Posts
    29
    Thanks guys, I got it working. I was fixing to plug in the first suggestion, when I happen to look down at the next code section and it said "If rng.Value > 1 Then", so it hit me, well I should prob. try that first to keep it consistant...hehe but it seemed to work. Thanks guys.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1