Trigger events on cell drag down or paste

[Blobajob88]

Hi,

Does anyone know how you would trigger events to all the cells that you drag down in a column. For example, when I change the date ordered column value it updates all the cells that have changed. Right now it only changes one.

 

[My Aswer Is This]

When I see your Date Ordered column I see nothing. And what column is this? Is it column("A")?

So if you have "Alpha" in Range("A1") and you Drag down to Range("A20") do you want "Alpha" In the Range("A1") to Range("A20").
If not provide more specifics.

 

[Blobajob88]

Hi

Sorry for being unclear. I would want to drag "Alpha" down in Range("A1")to "A20" for example and have, say, Range("B1") to "B20" change in response

 

[My Aswer Is This]

Hi

Sorry for being unclear. I would want to drag "Alpha" down in Range("A1")to "A20" for example and have, say, Range("B1") to "B20" change in response

You do know you can do that yourself without any code.

Put "Alpha" in Range("A1")
Put "Bravo" in Range("B1")
Select the Range("A1:B1")
In The bottom right corner of Range("B1") you should see a little square black box
Select the Box and drag down as far as you want.

 

[Blobajob88]

It has to be in code. Is there a way of doing it?

 

[My Aswer Is This]

You said:
Sorry for being unclear. I would want to drag "Alpha" down in Range("A1")to "A20" for example and have, say, Range("B1") to "B20" change in response

Change in Response??

Do you mean if you have "Alpha" in Range("A1")
Then you want Alpha in Range("A1" And B1
And fill down a certain number rows

 

[Blobajob88]

My current code reads like this but it only works if one cell is changed. I don't know how to get it to work if one more than one is changed at the same time, like if someone pastes in a load of values or drags down a cell value:

If Target.Count = 1 Then


        If IsEmpty(Target.Value) Then
        Application.EnableEvents = True
        Sheet1.Protect

            Exit Sub

            
        End If

        If Not IsDate(Target.Value) Then
        
        
        Target.Value = ""
        MsgBox "Please Enter A Valid Date", vbCritical, "Invalid Data Type"
        
        Target.Select
        Application.EnableEvents = True
        Sheet1.Protect
        Exit Sub
        
        End If
        

    Target.NumberFormat = "dddd dd mmmm yyyy"
    
        
    If Target.Value <> "" And Target.Offset(0, -4).Value <> "" And Target.Offset(0, -6).Value <> "" Then
    
    Target.Offset(0, -1).Value = WorksheetFunction.WorkDay(Target.Offset(0, -6).Value, Target.Offset(0, -4).Value)
    Target.Offset(0, -1).NumberFormat = "dddd dd mmmm yyyy"
    
    If Target.Offset(0, -1).Value >= Target.Value Then
    Target.Offset(0, -1).Interior.Color = rgbRed
    Target.Offset(0, -1).Font.Color = rgbBlack
    Target.Offset(0, -1).Font.Bold = True
    
    ElseIf Target.Offset(0, -1).Value < Target.Value Then
    Target.Offset(0, -1).Interior.Color = rgbGreen
    Target.Offset(0, -1).Font.Color = rgbBlack
    Target.Offset(0, -1).Font.Bold = True

    End If
    
    Else
    
    
    Target.Offset(0, -1).Interior.Color = rgbWhite
    Target.Offset(0, -1).Font.Color = rgbBlack
    
    End If
    
    
    
    If Target.Value <> "" And Target.Offset(0, -5).Value <> "" And Target.Offset(0, -6).Value <> "" Then
    
    Target.Offset(0, -2).Value = WorksheetFunction.WorkDay(Target.Offset(0, -6).Value, Target.Offset(0, -5).Value)
    Target.Offset(0, -2).NumberFormat = "dddd dd mmmm yyyy"
    
    If Target.Offset(0, -2).Value >= Target.Value Then
    Target.Offset(0, -2).Interior.Color = rgbRed
    Target.Offset(0, -2).Font.Color = rgbBlack
    Target.Offset(0, -2).Font.Bold = True
    
    ElseIf Target.Offset(0, -2).Value < Target.Value Then
    Target.Offset(0, -2).Interior.Color = rgbGreen
    Target.Offset(0, -2).Font.Color = rgbBlack
    Target.Offset(0, -2).Font.Bold = True

    End If
    

    
    End If

 

[My Aswer Is This]

My current code reads like this but it only works if one cell is changed. I don't know how to get it to work if one more than one is changed at the same time, like if someone pastes in a load of values or drags down a cell value:

If Target.Count = 1 Then


        If IsEmpty(Target.Value) Then
        Application.EnableEvents = True
        Sheet1.Protect

            Exit Sub

           
        End If

        If Not IsDate(Target.Value) Then
       
       
        Target.Value = ""
        MsgBox "Please Enter A Valid Date", vbCritical, "Invalid Data Type"
       
        Target.Select
        Application.EnableEvents = True
        Sheet1.Protect
        Exit Sub
       
        End If
       

    Target.NumberFormat = "dddd dd mmmm yyyy"
   
       
    If Target.Value <> "" And Target.Offset(0, -4).Value <> "" And Target.Offset(0, -6).Value <> "" Then
   
    Target.Offset(0, -1).Value = WorksheetFunction.WorkDay(Target.Offset(0, -6).Value, Target.Offset(0, -4).Value)
    Target.Offset(0, -1).NumberFormat = "dddd dd mmmm yyyy"
   
    If Target.Offset(0, -1).Value >= Target.Value Then
    Target.Offset(0, -1).Interior.Color = rgbRed
    Target.Offset(0, -1).Font.Color = rgbBlack
    Target.Offset(0, -1).Font.Bold = True
   
    ElseIf Target.Offset(0, -1).Value < Target.Value Then
    Target.Offset(0, -1).Interior.Color = rgbGreen
    Target.Offset(0, -1).Font.Color = rgbBlack
    Target.Offset(0, -1).Font.Bold = True

    End If
   
    Else
   
   
    Target.Offset(0, -1).Interior.Color = rgbWhite
    Target.Offset(0, -1).Font.Color = rgbBlack
   
    End If
   
   
   
    If Target.Value <> "" And Target.Offset(0, -5).Value <> "" And Target.Offset(0, -6).Value <> "" Then
   
    Target.Offset(0, -2).Value = WorksheetFunction.WorkDay(Target.Offset(0, -6).Value, Target.Offset(0, -5).Value)
    Target.Offset(0, -2).NumberFormat = "dddd dd mmmm yyyy"
   
    If Target.Offset(0, -2).Value >= Target.Value Then
    Target.Offset(0, -2).Interior.Color = rgbRed
    Target.Offset(0, -2).Font.Color = rgbBlack
    Target.Offset(0, -2).Font.Bold = True
   
    ElseIf Target.Offset(0, -2).Value < Target.Value Then
    Target.Offset(0, -2).Interior.Color = rgbGreen
    Target.Offset(0, -2).Font.Color = rgbBlack
    Target.Offset(0, -2).Font.Bold = True

    End If
   

   
    End If

I have no answer as to how you can do this

 

[GWteB]

The Count property of a Range object returns the number of individual cells within that range. Since your code checks for that and the rest of your code only executes when only one cell is involved, you could drop that conscious check, ie removing this line ...

If Target.Count = 1 Then

... and its associated End If. However ..., this will cause run-time errors because comparisons are made in the rest of your code that do not account for the multiple cell range, so many more adjustments are needed to be made.