VBA: When Sheet Changes - Issues with in cell formula

[spydey]

I have some simple code that I have used previously.
I have adapted to a different circumstance, however, I am not getting the desired effect.

Here is the code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range
    
    Rows.AutoFit
    
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    
    If Not Intersect(Range("G:G,I:I"), Target) Is Nothing Then
        For Each cell In Intersect(Range("G:G,I:I"), Target)
            Select Case cell.Column
                Case 7
                    If UCase(cell.Value) = "NO" Then
                        cell.Offset(0, 1) = "N/A"
                    Else
                        cell.Offset(0, 1).ClearContents
                    End If
                    
                Case 9
                    If IsEmpty(cell.Value) Then
                        cell.Offset(0, 2).ClearContents
                    ElseIf IsDate(cell.Value) And cell.Offset(0, 2).Value = "Review Required" Then
                        cell.Offset(0, 2) = "N/A"
                   End If
                    
            End Select
        Next cell
    End If

ErrorHandler:
    Application.EnableEvents = True
    If Err.Number <> 0 Then MsgBox Err.Description, vbCritical, "Error " & Err.Number
    
End Sub

The issue I am having is with Case 9.

Basically, if a date is input into a cell in column I, i.e. I2, and the same row in Column J states "Review Required", then the same row in column K should populate with "N/A"
I have tried with with just the criteria of column I and it works. The issue comes in when I incorporate column J into the equation.
I am guessing that it is because I have an in-cell formula in column J. Based upon certain criteria of the values of other cells, column J determines if it is "Review Required" or some other value.
But the formula sits within the cell. I think this is interfering with the code above, but am not sure.

I have also tried the following variations:

ElseIf IsDate(cell.Value) And UCase(cell.Offset(0, 2).Value) = "Review Required" Then

ElseIf IsDate(cell.Value) And cell.Offset(0, 2).Text = "Review Required" Then

ElseIf IsDate(cell.Value) And UCase(cell.Offset(0, 2).Text) = "Review Required" Then

Any thoughts on what might be the issue and why it is not updating correct?

-Spydey

 

[Yongle]

Try this

               Case 9
                    If IsEmpty(cell.Value) Then
                        cell.Offset(0, 2).ClearContents
                    ElseIf IsDate(cell.Value) Then
                        If cell.Offset(0, 2).Value = "Review Required" Then cell.Offset(0, 2) = "N/A"
                    End If

 

[spydey]

Yongle, thanks for the insight.

While that worked, I figured out my issue.

Once of my criteria was based off of offset(0,2) = Review Required, and then I was trying to change that same cell value to "N/A", when it should have been the following cell value, offset(0,3).

I do appreciate the help!! Thanks

-Spydey

 

[Yongle]

Glad you got it sorted