分享最近因为工作需要改写的一个VBA代码，当某个单元格首次填写内容后，记录下它的首次编辑时间，并当单元格内容发生修改时，首次编辑时间不发生变化
代码1：记录1格单元格的首次编辑时间保持不变
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
With Target
If .Count = 1 Then
If .Column = 7 And .Offset(0, 2) = "" Then
'G列输入内容，在I列记录时间
.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
.Offset(0, 2) = Now
End If
Else
If Intersect(Target, Range("G:G")) Is Nothing Then End
For Each rCell In Intersect(Target, Range("G:G"))
If rCell.Column = 7 And rCell.Offset(0, 2) = "" Then
rCell.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
rCell.Offset(0, 2) = Now
End If
Next
End If
End With
End Sub
代码2：记录2格单元格的首次编辑时间保持不变
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rCell As Range
With Target
If .Count = 1 Then
If .Column = 6 And .Offset(0, 2) = "" Then
'F列输入内容，在H列记录时间
.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
.Offset(0, 2) = Now
End If
If .Column = 7 And .Offset(0, 2) = "" Then
'G列输入人内容，在I列记录时间
.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
.Offset(0, 2) = Now
End If
Else
If Intersect(Target, Range("F:G")) Is Nothing Then End
For Each rCell In Intersect(Target, Range("F:G"))
If rCell.Column = 6 And rCell.Offset(0, 2) = "" Then
rCell.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
rCell.Offset(0, 2) = Now
End If
If rCell.Column = 7 And rCell.Offset(0, 2) = "" Then
rCell.Offset(0, 2).NumberFormat = "yyyy-m-d h:mm:ss"
rCell.Offset(0, 2) = Now
End If
Next
End If
End With
End Sub
操作步骤
1. 在EXCEL中 Alt+F11快捷键打开VBE编辑窗口
2. 选中要用VBA代码控制的工作表
3. 根据实际需求复制代码1或代码2
4.另存文件格式为“Excel启用宏的工作簿”，否则无法保存代码到文件里
