Search code examples
excelvba

VBA if another cell "MAINTENANCE" then return "CLOSED", otherwise leave cell empty for user to input text

So I am creating a worksheet that requires a cell to return the text "CLOSED" if another cell has text "MAINTENANCE". At first glance I tried using

=if(A2 = "MAINTENANCE", "CLOSED", "")

...and so on for every other row.

However, if I need to input text, I would have to erase the function first, it is not a big deal to do so, but I was just curious if there was a VBA way to do it without having to delete anything.

Example Below:

EXAMPLE:

Notice that every row that has "Maintenance" on column C, it autofills as "CLOSED" on the column B, and if column C is anything other than "MAINTENANCE", it leaves the column B blank for me to type.

Thanks in advance.

Solution
    • Save the change event code into worksheet module
    Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .CountLarge = 1 And .Column = 3 Then
            Application.EnableEvents = False
            If StrComp(.Value, "MAINTENANCE", vbTextCompare) = 0 Then
                .Offset(0, -1).Value = "CLOSED"
            Else
                .Offset(0, -1).Value = ""
            End If
            Application.EnableEvents = True
        End If
    End With
End Sub