Code to check for two conditions on a named range

[Sumeluar]

Good day - I have a code that needs tweaking for it to work as I intend it to, since I am very green when it comes to coding, I am asking for your assistance.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Database").Columns(2)) Is Nothing Then
If (Target.Value) = "N" And (Target.Offset(0, 1)) > "" Then
Target.Offset(0, 5).Value = "Not Started"
Else
Target.Offset(0, 5).Value = ""
End If
End If
En sub

What I am after is the following: From a named range "Database" I want column 5 to populate with "Not started" if column 2 equals "N" and column 3 contains text, I have tried many different ways and it works randomly backwards, if I type some text on column 3 and go back to column 2 it seems to work which is not the results I am looking for. Basically, I need both conditions to be true to populate column 5.

I appreciate beforehand any input or ideas.

Best regards!

 

[Joe4]

That is because you code is only set to fire when column B is updated, and you want it to fire when EITHER columns B or C are updated.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Run if an update to column 2 or column 3
    If (Target.Column = 2) Or (Target.Column = 3) Then
'       Get row of update
        r = Target.Row
        Application.EnableEvents = False
'       Check columns B and C
        If (Cells(r, "B") = "N") And (Cells(r, "C") <> "") Then
'           Update column E
            Cells(r, "E") = "Not started"
        Else
            Cells(r, "E").ClearContents
        End If
        Application.EnableEvents = True
    End If

End Sub

 

[Sumeluar]

That is because you code is only set to fire when column B is updated, and you want it to fire when EITHER columns B or C are updated.
Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim r As Long

'   Exit if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
   
'   Run if an update to column 2 or column 3
    If (Target.Column = 2) Or (Target.Column = 3) Then
'       Get row of update
        r = Target.Row
        Application.EnableEvents = False
'       Check columns B and C
        If (Cells(r, "B") = "N") And (Cells(r, "C") <> "") Then
'           Update column E
            Cells(r, "E") = "Not started"
        Else
            Cells(r, "E").ClearContents
        End If
        Application.EnableEvents = True
    End If

End Sub

Joe4 - Thanks, that code works. Appreciate the prompt reply and solution.

 

[Joe4]

You are welcome.
Glad I was able to help!