Subtract one day in date when Value selected

[tobysimon]

Hello VBA experts

Hoping I'd get help on below problem.

I want to subtract one day in dates range ("J3 to J8") when Value selected from drop-down Cell ("H21"). The drop down reference is from Data validation from same sheet but hidden. Drop down values "Yesterday", "Yesterday1" and "Blank" (No changes should be made when "blank" is selected).

Would be really helpful if I get vba for this action to execute. Appreciate your help.

 

[Joe4]

Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range

'   Exit if cell H21 not updated
    If Intersect(Target, Range("H21")) Is Nothing Then Exit Sub
    
'   Only run if H21 is "Yesterday" or "Yesterday1"
    If Range("H21") = "Yesterday" Or Range("H21") = "Yesterday1" Then
    
'       Update cells J3:J8
        Application.EnableEvents = False
        For Each cell In Range("J3:J8")
'          Subtract one from date if cell has a value
            If cell > 0 Then cell.Value = cell.Value - 1
        Next cell
        Application.EnableEvents = True
    End If

End Sub

This will automatically subtract one from the dates in cells J3:J8 whenever H21 is changed to "Yesterday" or "Yesterday1".

 

[tobysimon]

Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cell As Range

'   Exit if cell H21 not updated
    If Intersect(Target, Range("H21")) Is Nothing Then Exit Sub
   
'   Only run if H21 is "Yesterday" or "Yesterday1"
    If Range("H21") = "Yesterday" Or Range("H21") = "Yesterday1" Then
   
'       Update cells J3:J8
        Application.EnableEvents = False
        For Each cell In Range("J3:J8")
'          Subtract one from date if cell has a value
            If cell > 0 Then cell.Value = cell.Value - 1
        Next cell
        Application.EnableEvents = True
    End If

End Sub

This will automatically subtract one from the dates in cells J3:J8 whenever H21 is changed to "Yesterday" or "Yesterday1".

Thank you Joe4. It worked

 

[Joe4]

You are welcome.
Glad I was able to help!