Finding text within a row, then finding text within that column

Viewed 72

I'm trying to find a cell within a set row with a specific string ("Final Mark", then search the cells below that text. If that cell contains a specific piece of text ("Fail"), I then need a msgbox to pop-up.

This is for a workbook that collects data on marked assignments. I've tried the below code, but it's not working.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim FinalMark As Range
    Set FinalMark = Rows(5).Find(what:="Final Mark", LookIn:=xlValues, lookat:=xlWhole)
    If Not FinalMark Is Nothing Then
    If Not FinalMark.Column.Find(what:="Fail", LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True) Is Nothing Then
        MsgBox "Input reason for fail in Further Notes.", vbInformation
    End If
    End If
End Sub

I was hoping it would search the column where I found the text "Final Mark", but it doesn't.

1 Answers

Change

If Not FinalMark.Column

to

If Not Columns(FinalMark.Column)
Related