Capture Multiple Cell change

[spydey]

I have code that watches three different columns for changes.

It works great when only one cell changes at a time.

But if I copy and paste multiple cells of data in to the columns at a single time, it doesn't work.

I am pretty sure that I know why, but I can't figure out how to change it to watch for multiple cell changes and only take effect for the rows where the cells changed.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim wb As Workbook
Dim ws As Worksheet


Set wb = ThisWorkbook
Set ws = wb.Worksheets(1)


[B]If Target.Cells.Count > 1[/B] Then Exit Sub


    If [B]Target.Count = 1[/B] And Target.Column = 5 Then
        If IsEmpty(Target.Value) Then
            Target.Offset(0, 2).ClearContents
        Else
            If IsDate(Target.Value) And IsEmpty(Target.Offset(0, 1).Value) Then
                Target.Offset(0, 2).ClearContents
            Else
                If IsDate(Target.Value) And IsDate(Target.Offset(0, 1).Value) Then
                    Target.Offset(0, 2) = Application.WorksheetFunction.Days(Target.Offset(0, 1).Value, Target.Value) + 1
                End If
            End If
        End If


ElseIf [B]Target.Count = 1[/B] And Target.Column = 6 Then


    If IsEmpty(Target.Value) Then
        Target.Offset(0, 1).ClearContents
    Else
        If IsDate(Target.Value) And IsEmpty(Target.Offset(0, -1).Value) Then
            Target.Offset(0, 1).ClearContents
        Else
            If IsDate(Target.Value) And IsDate(Target.Offset(0, -1).Value) Then
                Target.Offset(0, 1) = Application.WorksheetFunction.Days(Target.Value, Target.Offset(0, -1).Value) + 1
            End If
        End If
    End If


ElseIf [B]Target.Count = 1[/B] And Target.Column = 8 Then


    If UCase(Target.Value) = "PASS" Or UCase(Target.Value) = "FAIL" Then
        Target.Offset(0, 1) = Date
    Else
        Target.Offset(0, 1).ClearContents
    End If
End If


End Sub

I have made bold where I think the issue is stemming from, but again, I don't know exactly how to go about changing it to capture any and all changes to those specific columns, regardless if it is a single cell or multiple cells.

Any thoughts, ideas, suggestions?

Thanks in advance!

-Spydey

 

[AlphaFrog]

Try something like this (not tested)...

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Worksheet_Change([color=darkblue]ByVal[/color] Target [color=darkblue]As[/color] Range)
    
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range
    
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]GoTo[/color] ErrorHandler
    Application.EnableEvents = [color=darkblue]False[/color]
    
    [color=darkblue]If[/color] [color=darkblue]Not[/color] Intersect(Range("E:F,H:H"), Target) [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
        [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Intersect(Range("E:F,H:H"), Target)
            [color=darkblue]Select[/color] [color=darkblue]Case[/color] cell.Column
                [color=darkblue]Case[/color] 5
                    [color=darkblue]If[/color] IsEmpty(cell.Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 2).ClearContents
                    [color=darkblue]ElseIf[/color] IsDate(cell.Value) And IsEmpty(cell.Offset(0, 1).Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 2).ClearContents
                    [color=darkblue]ElseIf[/color] IsDate(cell.Value) And IsDate(cell.Offset(0, 1).Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 2) = Application.WorksheetFunction.Days(cell.Offset(0, 1).Value, cell.Value) + 1
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Case[/color] 6
                    [color=darkblue]If[/color] IsEmpty(cell.Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 1).ClearContents
                    [color=darkblue]ElseIf[/color] IsDate(cell.Value) And IsEmpty(cell.Offset(0, -1).Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 1).ClearContents
                    [color=darkblue]ElseIf[/color] Is[color=darkblue]Date[/color](cell.Value) And IsDate(cell.Offset(0, -1).Value) [color=darkblue]Then[/color]
                        cell.Offset(0, 1) = Application.WorksheetFunction.Days(cell.Value, cell.Offset(0, -1).Value) + 1
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
                [color=darkblue]Case[/color] 8
                    [color=darkblue]If[/color] UCase(cell.Value) = "PASS" [color=darkblue]Or[/color] UCase(cell.Value) = "FAIL" [color=darkblue]Then[/color]
                        cell.Offset(0, 1) = Date
                    [color=darkblue]Else[/color]
                        cell.Offset(0, 1).ClearContents
                    [color=darkblue]End[/color] [color=darkblue]If[/color]
            [color=darkblue]End[/color] [color=darkblue]Select[/color]
        [color=darkblue]Next[/color] cell
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
ErrorHandler:
    Application.EnableEvents = [color=darkblue]True[/color]
    [color=darkblue]If[/color] Err.Number <> 0 [color=darkblue]Then[/color] MsgBox Err.Description, vbCritical, "Error " & Err.Number
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[spydey]

@AlphaFrog

Thanks for the code. I will give it a try and report back.

Out of curiosity, how does the case work into this? Meaning, Case, 5, Case 6, Case 8. I am guessing that they are related to the column number of E, F, & H.

Is the cell.column identifying the number for the case?

I learn something every day!!

-Spydey

 

[spydey]

@AlphaFrog

So I tried it, and it worked flawlessly!!! Thank you so much for helping, and I noticed that it appears you also cleaned up my code. I appreciate that, thank you.

-Spydey

 

[AlphaFrog]

Meaning, Case, 5, Case 6, Case 8. I am guessing that they are related to the column number of E, F, & H.

Is the cell.column identifying the number for the case?

Spot on.

You're welcome. Thanks for the feedback.