Excel VBA Msg Box when cell meets criteria

Iangough16 - Feb 9, 2009 at 06:33 AM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Mar 13, 2010 at 10:13 PM

Hi this will probably be very simple for someome however I am having a nightmare day, all I want is a vba formual which will bring up a message box when a cell is showing a specific criteria.

Can anyone help?



2 replies

This code assumes that what you are looking for is in column A.

Private Sub Find_Criteria()

Dim i
Dim r
r = Range("A65536").End(xlUp).Row
i = 1

For I = I To r

If Range("A" & i) = "criteria" Then

MsgBox "Found" & " " & Range("A" & i).Address

End If

Next i

End Sub

If you are looking for certain criteria in a range of cells, then try something like this.

Private Sub Find_Criteria()

Dim I As Variant
Dim FindRange As Range
Set FindRange = Range("A1:K50")

For Each I In FindRange

If I = "criteria" Then

MsgBox "Found" & " " & i.Address

End If

Next i

End Sub