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