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.