VBA - Find value and overwrite it with the content of the cell above it

[bnpdx]

Hello

I hope you can help me with this.

I have a spreadsheet where columns D through M have cells with values "TARGET?".

I need VBA code that would do the following:
1) Find cells with these exact values (finding cells containing "TARGET?" is not enough as it can't be "TARGET? - BLAH"; it has to be exactly "TARGET?")
2) Overwrite the values in such cell with the content of the cell directly above it.
3) Mark new values (previously "TARGET?") using red font color.

Any help would be much appreciated.

Thank you!!

 

[JLGWhiz]

Maybe this

Sub t()
Dim fn As Range, loc As Range
    With ActiveSheet
        Set fn = Intersect(.UsedRange, .Range("D:M")).Find("Target?", , xlValues, xlWhole)
            If Not fn Is Nothing Then
               
                Do
                    Set loc = fn
                    If fn.Value = "Target?" Then
                    End If
                    Set fn = Intersect(.UsedRange, .Range("D:M")).FindNext(fn)
                    loc = loc.Offset(-1).Value
                    loc.Font.Color = vbRed
                Loop While Not fn Is Nothing
            End If
    End With
End Sub

 

[bnpdx]

Maybe this

Sub t()
Dim fn As Range, loc As Range
    With ActiveSheet
        Set fn = Intersect(.UsedRange, .Range("D:M")).Find("Target?", , xlValues, xlWhole)
            If Not fn Is Nothing Then
              
                Do
                    Set loc = fn
                    If fn.Value = "Target?" Then
                    End If
                    Set fn = Intersect(.UsedRange, .Range("D:M")).FindNext(fn)
                    loc = loc.Offset(-1).Value
                    loc.Font.Color = vbRed
                Loop While Not fn Is Nothing
            End If
    End With
End Sub

Thank you very much! This worked perfectly.

 

[JLGWhiz]

You're welcome,
regards, JLG