Macro Question

[mcintoshmc]

I've been using this macro for years successfully. It basically enters the current date in a row in column L once a cell in column I is edited.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range
Dim rCell As Range
Dim tCell As Range

Set rInt = Intersect(Target, Range("I:I"))
If Not rInt Is Nothing Then
For Each rCell In rInt
With rCell.Offset(0, 3)
.Value = Now
.NumberFormat = "mmm d, yyyy"

End With
Next
End If
End Sub

I now want to highlight a row if the date is within the current calendar week and I'm using the below formula.

=TODAY()-WEEKDAY(TODAY(), 3)=$L2-WEEKDAY($L2, 3)

It works only when the month date and year is in the cell located in column L. But, the macro above returns the date and time (6/8/2023 2:39:39 PM), and therefore leaves the formula inoperable.

I either need a formula that will work with the time included, or a different macro that will return only the date and not the time.

Since everything I know about Excel was learned from here, I figure I'd ask you beautiful people. Any assistance provided is appreciated. Thanks.

 

[Micron]

perhaps just edit to .Value = DateValue(Now()) ? As long as the cell contains a valid date with a valid time portion you shouldn't raise an error. I suspect cell formatting would have to be Date though (or maybe you would get away with General, but Date should be best).
Please enclose code in vba code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[mcintoshmc]

that didn't work for me

 

[Micron]

Seems to work for me. The results of your code in L1 and my suggested change in L2.
Note that while formatted as mm dd, yyyy the value in L1 is actually 6/13/2023 12:45:06 PM, whereas in L2 it is 6/13/2023. Thus your formula is False for L1 and True for L2, indicating that it works. If not working for you, then I suggest you step through your code and follow it through. Perhaps you need to disable events for just this procedure - other code might be being called by the changes this one makes to your sheet. IMO, if you alter application settings during a procedure, it's wise to use an error handler lest they remain altered and you end up pulling hair out trying to figure out why something no longer works or runs.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Jun 13, 2023​

FALSE​

2​

Jun 13, 2023​

TRUE​

Also, I recommend you put your versions info in your signature. Sometimes the reason why something works for one and not another is different Excel versions. Not that this would help me too much, but others are very aware of version differences.

 

[mcintoshmc]

Seems to work for me. The results of your code in L1 and my suggested change in L2.
Note that while formatted as mm dd, yyyy the value in L1 is actually 6/13/2023 12:45:06 PM, whereas in L2 it is 6/13/2023. Thus your formula is False for L1 and True for L2, indicating that it works. If not working for you, then I suggest you step through your code and follow it through. Perhaps you need to disable events for just this procedure - other code might be being called by the changes this one makes to your sheet. IMO, if you alter application settings during a procedure, it's wise to use an error handler lest they remain altered and you end up pulling hair out trying to figure out why something no longer works or runs.

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

N​

1​

Jun 13, 2023​

FALSE​

2​

Jun 13, 2023​

TRUE​

Also, I recommend you put your versions info in your signature. Sometimes the reason why something works for one and not another is different Excel versions. Not that this would help me too much, but others are very aware of version differences.

thank you. I got it to work.

 

[Micron]

Thanks for the recognition and glad you got it to work. In case you're interested in how to make use of an error handler to ensure events are re-enabled in the event of an error:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rInt As Range, rCell As Range, tCell As Range

On Error GoTo errHandler
Application.EnableEvents = False
Set rInt = Intersect(Target, Range("I:I"))
If Not rInt Is Nothing Then
     For Each rCell In rInt
          With rCell.Offset(0, 3)
               .Value = DateValue(Now())
               .NumberFormat = "mmm d, yyyy"
          End With
     Next
End If

exitHere:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub