Macro or other to Search a date and replace with new date with a condition

[Antispam246]

I have two date columns B and C both formatted to date xx/xx/xx

I want to be able to (ideally) run a macro which searches for a specific date in column B and replaces it with a new one but only if column C is empty.

As the dates that will change further down the line will be manually updated as they are random it can't be automatic and I can manually edit the date requirements in said macro prior to the next run.

Any help appreciated

 

[My Aswer Is This]

Try this:

Private Sub CommandButton1_Click()
'Modified  10/6/2019  5:05:41 AM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim i As Long
Dim Lookfor As String
Dim ReplaceWith As String
Lookfor = InputBox("Enter date to look for", "Today is  " & Date)
ReplaceWith = InputBox("Enter date To enter", "Today is  " & Date)

    For i = 1 To Lastrow
        If Cells(i, 2).Value = Lookfor And Cells(i, 3).Value = "" Then Cells(i, 2).Value = ReplaceWith
    Next
    
End Sub

 

[DanteAmor]

Try this

Change 2019,09,21 (yyyy,mm,dd) For the date to replace
Change 2019,10,15 (yyyy,mm,dd) For the new date

Sub ReplaceDate()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate("=IF({1},IF(" & .Address & "=DATE([COLOR=#ff0000]2019,09,21[/COLOR]),IF(" & .Offset(, 1).Address & "="""",DATE([COLOR=#0000ff]2019,10,15[/COLOR])," & .Address & ")," & .Address & "))")
  End With
End Sub

 

[Antispam246]

Thank you both, I ran into issues with the first lot of code, an error stating macros weren't enabled. The second lot Sub ReplaceDate() worked perfectly.

Going to do abit more testing on the test sheet before converting the original to macro enabled but all seems good, thank you!

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.