Trigger events on cell drag down or paste

Blobajob88

New Member
Joined
Mar 27, 2020
Messages
40
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

1603964762974.png
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 27, 2020
Messages
40
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 27, 2020
Messages
40
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Mar 27, 2020
Messages
40
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,247
Office Version
  1. 2013
Platform
  1. Windows
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:

VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,284
Office Version
  1. 2013
Platform
  1. Windows
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 ...
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,295
Messages
5,571,394
Members
412,386
Latest member
Yasaman
Top