HELP: Display a message when one of the names is displayed in a ra

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi All,

How can I display a message box if a range (B1:B10) has one of these values
(tom,ross,chad,sam,josh,jill) on a button click (DisplayMessage)

Here is what I have so far but I get a type mismatch error:

Sub DisplayMessage_Click()

If Worksheets("Sheet1").Range("B1:B10) = "tom" or "ross" or "chad" or "sam"
or "josh" or "jill") Then

MsgBox ("Name present")

End If

End sub

Thanks in advance
 
You need

with worksheets("Sheet1").range("B1")
if .value = "Sam" or .value = "Tom" ... or .value = "Jill" then
msgbox "Name present"
end if
end with

or if the cells B1 to B10 aren't merged then

dim rng as range, c as range
set rng = worksheets("Sheet1").range("B1:B10")
for each c in rng
with c
if .value = "Sam" or .value = "Tom" ... or .value = "Jill" then
msgbox "Name present"
end if
end with
next c

end with
 
Back
Top