How to code this macro? Referring to following file, I would like to know on how

Question

How to code this macro?

Referring to following file, I would like to know on how to code this macro based on following conditions:

The following code is fixed for cell B3, but I will get another item in future under column C ... IH etc

If I need to copy and paste the similar code for different columns, then there are too much coding.

Does anyone have any suggestions on how to use a for loop to perform checking on all columns with a single set of coding?

Thanks in advance for any suggestions

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("B3")) Is Nothing Then
        Range("B4") = Now()
        Range("B8:B20").Copy
        Range("B10").Select
        ActiveSheet.Paste
        Range("B3:B4").Copy
        Range("B8").Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Range("B1").Select
    End If

End Sub

Explanation / Answer

&^%$*&^*&^&;'

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
If Not Intersect(Target, Range("B3:IH3")) Is Nothing Then
Application.EnableEvents = False
lastrow = Cells(Rows.Count, Target.Column).End(xlUp).Row
If Target.Value = "" Then
   Cells(3, Target.Column).Resize(lastrow - 2).ClearContents

Else

    Target.Offset(1) = Now()
     Target.Offset(5).Resize(13).Copy Target.Offset(7)
     Target.Resize(2).Copy Target.Offset(5)
     Application.CutCopyMode = False
     Cells(1, Target.Column).Select
End If
End If
Application.EnableEvents = True
End Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.