VBA代码效率 - Excel范围

Question

我在下面运行的代码，可以正常运行。

问题：是否有更多编写此代码的方法？（短）

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Not Intersect(Target, Range("D24")) Is Nothing Then
              Sheet2.Range("D24") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
     If Not Intersect(Target, Range("D20")) Is Nothing Then
              Sheet2.Range("D20") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
    If Not Intersect(Target, Range("D25")) Is Nothing Then
              Sheet2.Range("D25") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
     If Not Intersect(Target, Range("D27")) Is Nothing Then
              Sheet2.Range("D27") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
     If Not Intersect(Target, Range("D28")) Is Nothing Then
              Sheet2.Range("D28") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
    If Not Intersect(Target, Range("D30")) Is Nothing Then
              Sheet2.Range("D30") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
    If Not Intersect(Target, Range("D31")) Is Nothing Then
              Sheet2.Range("D31") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
    End If
    If Not Intersect(Target, Range("D32")) Is Nothing Then
              Sheet2.Range("D32") = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
End Sub

Answer 1

假设，文本不在同一纸上，这个？

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("D20,D24,D25,D27,D28,D30,D31,D32")) Is Nothing Then Exit Sub
'Cancel=True
Sheet2.Range(Target.Address).Value = "Prepared By" & "  " & Environ("Username") & "  " & Format(Now(), "yyyy-MM-dd hh:mm:ss")
End Sub

Answer 2

另一个选项，不使用Intersect：

---

Option Explicit
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    With Target
        If .Column = 4 Or .Column = 5 Then
            Select Case .Row
                Case 20, 24, 25, 27 To 28, 30 To 32
                    Application.EnableEvents = False
                    .Value2 = "Prepared By" & "  " & Environ("Username")
                    .Value2 = .Value2 & "  " & Format(Now, "yyyy-MM-dd hh:mm:ss")
                    Application.EnableEvents = True
            End Select
        End If
    End With
End Sub

---