need vba code to chance the value in G:G column depepends upon rule

Try this:

Option Explicit

Sub Test()

Dim vArrayF, vArrayG
Dim lLR As Long, i As Long

With Sheets("DATA")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
    vArrayF = .Range("F2:F" & lLR)
    vArrayG = .Range("G2:G" & lLR)
    For i = LBound(vArrayF) To UBound(vArrayF)
        If vArrayG(i, 1) >= DateValue("01 May 2015") And vArrayG(i, 1) <> "NO Prior" Then
            vArrayG(i, 1) = 999
        Else
            If vArrayG(i, 1) = 0 Or vArrayG(i, 1) = "NO Prior" Then
                vArrayG(i, 1) = vArrayF(i, 1)
            End If
        End If
    Next 'i
    ' for testing and comparison
    .Range("I2").Resize(lLR - 1).Value = vArrayG
    ' live ...
    '.Range("G2").Resize(lLR - 1).Value = vArrayG
End With 'Sheets("DATA")

End Sub

Regards, TMS

You're welcome.