Results 1 to 9 of 9

Need to have 2 worksheet selection.change rules in 1 worksheet

Threaded View

winwall Need to have 2 worksheet... 03-14-2014, 11:15 AM
judgeh59 Re: Something missing - Not... 03-14-2014, 11:42 AM
winwall Re: Need to have 2 worksheet... 03-14-2014, 01:35 PM
judgeh59 Re: Need to have 2 worksheet... 03-14-2014, 01:58 PM
judgeh59 Re: Need to have 2 worksheet... 03-14-2014, 02:27 PM
winwall Re: Need to have 2 worksheet... 03-14-2014, 02:36 PM
winwall Re: Need to have 2 worksheet... 03-14-2014, 04:44 PM
judgeh59 Re: Need to have 2 worksheet... 03-14-2014, 04:46 PM
winwall Re: Something missing - Not... 03-14-2014, 02:18 PM
  1. #1
    Forum Contributor
    Join Date
    03-04-2014
    Location
    calgary, canada
    MS-Off Ver
    Excel 2013
    Posts
    108

    Need to have 2 worksheet selection.change rules in 1 worksheet

    OK. so attached is a file that when cells in column F and G are clicked in - they auto populate with "NA" and "U". now this is all working fine - EXCEPT for the fact that rows 387-391 need to populate with a "y" and "n" respectively. now i can actually see when i click on the cell - the Y or N populates and then it changes to "NA" and "U". i am attaching the file. all the code is on the sheet "fresh thinking audit".

    if someone could PLEASE see what i have to do to not have two codes run on the same cells i would be so grateful!!!

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim MyStr As String, MyVal As String, x As Long
    Dim y As Integer
    Dim i As Integer
    
    If Target.Interior.ColorIndex = 1 Then Exit Sub    'for black cell color
    If Target.Interior.ColorIndex = 40 Then Exit Sub   'for service at top of sheet
    
    
    
    
    If Not Intersect(Target, Range("G387:G391")) Is Nothing And Target.Cells.Count = 1 Then
        If Target.Value <> "" Then
            Target.Value = ""
            Exit Sub
        Else
            Target.Value = "N"
        End If
    End If
    If Not Intersect(Target, Range("F387:F391")) Is Nothing And Target.Cells.Count = 1 Then
        If Target.Value <> "" Then
            Target.Value = ""
            Exit Sub
        Else
            Target.Value = "Y"
        End If
    End If
    
    
    On Error Resume Next
    Application.EnableEvents = False
    
    If Target.Address = "$C$8" Then
        Target = Date
        Target.NumberFormat = "mm/dd/yyyy"
    ElseIf Target.Address = "$H$8" Then
        Target = Time
        Target.NumberFormat = "hh:mm"
    ElseIf (Target.Row > 24) And (Target.Row < 392) Then
    
            Select Case Target.Column
                Case 6
                    MyStr = "NA"
                Case 7
                    MyStr = "U"
                Case Else
                    MyStr = ""
            End Select
        
            If Len(MyStr) > 0 Then
                If InStr(Range("B" & Target.Row), "Speed of Service") = 0 Then
                    If Target = MyStr Then
                        If Target.Interior.ColorIndex = 15 Then
                            y = 1
                            MyVal = ""
                            While Target.Offset(y).Interior.ColorIndex = -4142
                                If Target.Offset(y) = "" Then
                                    MyVal = MyStr
                                End If
                                y = y + 1
                            Wend
                            y = y - 1
                            
                            'If Target.Offset(2) = MyStr Then MyVal = "" Else MyVal = MyStr
                            
                            'x = 1
                            If Not (MyStr = "U") Then
                             '   Do While Range("B" & Target.Row + x).Interior.ColorIndex = 2 Or Range("B" & Target.Row + x).Interior.ColorIndex = -4142
                             '       If Len(Range("B" & Target.Row + x)) > 0 Then Target.Offset(x) = MyVal
                             '       x = x + 1
                             '   Loop
                                For x = 1 To y
                                   Target.Offset(x) = MyVal
                                Next x
                            End If
                        ElseIf Target.Interior.ColorIndex <> 15 Then
                            
                            If MyStr = "U" Then
                                For i = 1 To WorksheetFunction.CountA(Sheets("sheet1").Range("a:a"))
                                    If Sheets("sheet1").Range("a" & i) = Target.Row Then
                                        Sheets("sheet1").Range(i & ":" & i).Delete
                                        i = i - 1
                                    End If
                                Next i
                            End If
                            Target = ""
                        End If
                    Else
                        If Target.Value = MyStr Then
                            Target = ""
                        Else
                            If Range("B" & Target.Row + x).Interior.ColorIndex = 2 Or Range("B" & Target.Row + x).Interior.ColorIndex = -4142 Then
                                If Len(Range("B" & Target.Row + x)) > 0 Then
                                    If MyStr = "U" Then
                                        i = WorksheetFunction.CountA(Sheets("sheet1").Range("a:a")) + 6
                                        Sheets("sheet1").Range("a" & i) = Range("a" & Target.Row)
                                        Sheets("sheet1").Range("b" & i) = Range("b" & Target.Row)
                                    End If
                                    Target = MyStr
                                    
                                    End If
                                End If
                            End If
                        End If
                    End If
            End If
            If (Target.Column = 7) And (Target = "U") And (Range("J" & Target.Row) = "x") Then
                Sheets("sheet4").Range("b" & WorksheetFunction.CountA(Sheets("sheet4").Range("b:b")) + 11) = Range("B" & Target.Row).Value
                'Sheets("Sheet4").Range("A" & Rows.Count).End(xlUp).End(xlUp).Offset(2).Value = Range("B" & Target.Row).Value
            End If
    End If
    
    ErrorExit:
        Application.EnableEvents = True
    End Sub
    Attached Files Attached Files
    Last edited by winwall; 03-14-2014 at 01:35 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro is not working as per the offset and hence missing data..
    By kishoremcp in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 06-26-2013, 09:41 AM
  2. Index-Match formula is not working ...whats missing ?
    By raad in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-16-2012, 01:41 PM
  3. Deleting missing references - not working
    By ghh3rd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2012, 04:03 PM
  4. DisplayAlerts = False not working for formula that points to a missing tab
    By iggydfrog in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-17-2012, 05:30 PM
  5. Excel 2007 : Working with Charts and Missing Data
    By faucetguru in forum Excel General
    Replies: 4
    Last Post: 10-07-2010, 08:01 PM

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