Formula for creating drop down list while using if function

Hemanth_R VBA is likely the best way to achieve this automatically; however, if you don't want to use VBA, you could at the very least use conditional formatting to highlight cells if a date is entered in column C but the value in column B does not equal "Completed", or if a value in column B equals "Completed" but the date is not yet entered in column C.

The formula to apply Conditional Formatting to column B is:

=AND($B2<>"Completed", $C2<>"")

Conditional Formatting Column B

And the formula to apply Conditional Formatting to column C is:

=AND($B2="Completed", $C2="")

Conditional Formatting Column C

Having said that, if you still want to use VBA to automatically update the value in column B when a date is input in column C, open the Visual Basic Editor (Alt+F11), double-click on the applicable sheet name in the VBA Project pane, then paste the following code in the worksheet module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop macro if more than one cell was changed
    If Target.Cells.Count > 1 Then Exit Sub

'Check if value was changed in Column C
    If Not Intersect(Target, Me.Columns(3)) Is Nothing Then
    ' update the task status in the adjacent cell in Column B
        If Not Target.Value = vbNullString Then
            Target.Offset(, -1).Value = "Completed"
        Else
            Target.Offset(, -1).Value = "In Progress"
        End If
    End If

End Sub

Or, as an alternative approach, to automatically add today's date to column C when the value in column B is changed to "Completed", use the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'Stop macro if more than one cell was changed
    If Target.Cells.Count > 1 Then Exit Sub

'Check if value was changed in Column B
    If Not Intersect(Target, Me.Columns(2)) Is Nothing Then
    ' update the completion date in the adjacent cell in Column C
        If Target.Value = "Completed" Then
            Target.Offset(, 1).Value = Date
        Else
            Target.Offset(, 1).ClearContents
        End If
    End If

End Sub

Cheers!