Sub MyDateFill()
Dim cell As Range
Application.ScreenUpdating = False
' Loop through range K6:K27
For Each cell In Range("K6:K27")
' Check to see that col K > 0 and col L is empty
If (cell > 0) And (cell.Offset(0, 1) = "") Then
' Put current date in column L
cell.Offset(0, 1) = Date
End If
Next cell
Application.ScreenUpdating = True
End Sub
Yes, that is a bit different. We can make automated code that runs automatiically upon data being manually entered into certain cells (like your range K6:K27). But that code does not know the previous value of the value you just updated (I think there is a way to do that, but it gets a bit tricky, and I would need to see if I can look it up and find it).How about if the K6 cell already has 0 and I change it to 1. I am sorry that I did not make myself clear. Mainly I want it to fill in the date when I change from 0 to any number.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim cell As Range
Application.ScreenUpdating = False
' See if any cells updated in range K6:K27
Set rng = Intersect(Target, Range("K6:K27"))
' Exit sub if no cells updated in our watched range
If rng Is Nothing Then Exit Sub
' Loop through all cells just updated in our range
For Each cell In rng
' Check to see if value is greater than 0 and column L blank
If (cell > 0) And (cell.Offset(0, 1) = "") Then
' Update column L with current time
cell.Offset(0, 1) = Date
End If
Next cell
Application.ScreenUpdating = True
End Sub
'Declare Global Variable
Dim prev As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Get value of cell upon first entering it
prev = Target.Value
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
' Exit if multiple cells updated at once
If Target.CountLarge > 1 Then Exit Sub
' Exit sub if no cells updated in our watched range
If Intersect(Target, Range("K6:K27")) Is Nothing Then Exit Sub
' Check to see if previous value is zero, new value is greater than 0, and column L blank
If (prev = 0) And (Target > 0) And (Target.Offset(0, 1) = "") Then
' Update column L with current time
Target.Offset(0, 1) = Date
End If
End Sub
I think you misread the question. There is no mention of column J, just columns K and L.hi, joe i would ask about function offset my knowledge is simple about it but as i see many codes if you move from right to left should the value -1 not 1 as you case if use 1 it moves from left to right should in col j not l but the mystery when you use 1 it moves to col j actually i tested your code but i don't understand about function offset may you explain or correct me how is it
no i don't misread the question i know your code works for col k,l when i mentioned col j as what i know i was wanting for you inform me the right way how the offset works so if i would move from col k to j or i it supposes like this (0,-1) or (0,-2)?I think you misread the question. There is no mention of column J, just columns K and L.