Change cell data automatically to "Approved" once cells I5 and J5 have data added to them

Question

I need my cell H5 to automatically change to "Approved" once my team enters their names in cells I5 and J5. This will be in the sharepoint and the cells are all data validation based from a setup tab. On the same note, I need it to remain "Pending" until those names are added to cells I5 and J5 collectively. So it would remain "Pending" if only one name is added.

Answer 1

Hi,

For this, you can use a combination of data validation, formulas, and VBA.

1. Create a setup tab where you can specify the names that can be entered in cells I5 and J5. Assume this setup is on the "Setup" sheet, with column A containing the list of names.
2. Use a formula in cell H5 of your main sheet to calculate the status based on the values in cells I5 and J5. Let us use the IF function for this. =IF(AND(ISNUMBER(MATCH(I5, Setup!$A$1:$A$100, 0)), ISNUMBER(MATCH(J5, Setup!$A$1:$A$100, 0))), "Approved", "Pending")
3. This formula determines whether the names in cells I5 and J5 appear in the list on the "Setup" sheet. If they are, it returns "Approved"; otherwise, "Pending."
4. Use the following VBA code to automatically change the status to "Approved" when both names are entered.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim setupSheet As Worksheet
    Dim approvalCell As Range

    ' Set references to the Setup sheet and the H5 cell
    Set setupSheet = ThisWorkbook.Sheets("Setup")
    Set approvalCell = Me.Range("H5")

    ' Check if the changed cells are I5 or J5
    If Intersect(Target, Me.Range("I5, J5")) Is Nothing Then Exit Sub

    ' Check if both names are in the Setup sheet
    If IsNumberInList(Me.Range("I5").Value, setupSheet.Range("A:A")) _
        And IsNumberInList(Me.Range("J5").Value, setupSheet.Range("A:A")) Then
        ' Set H5 to "Approved"
        approvalCell.Value = "Approved"
    Else
        ' Set H5 to "Pending"
        approvalCell.Value = "Pending"
    End If
End Sub

Function IsNumberInList(ByVal value As Variant, rng As Range) As Boolean
    ' Check if the value is in the specified range
    IsNumberInList = Not IsError(Application.Match(value, rng, 0))
End Function

This code checks for changes in cells I5 and J5 and updates the status in H5 accordingly.

Make sure your Excel settings allow macros to run. You may need to enable macros when you open the workbook.

Try this setup and let me know if it works.

Regards,

Tanay