# Message Box if cells contain criteria

##### Active Member
Hi Guys,

Is it possible to do the following:

If A3:A5 contains X then message box shows hit
If K2:N2 contains X then message box shows hit
If K10:K14 contains X then message box shows hit
Else show Miss

Thanks

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### Yongle

##### Well-known Member
TRY:
Code:
``````Sub Messages()
Dim a, cel As Range, msg As String
For Each a In Array("A3:A5", "K2:N2", "K10:K14")
For Each cel In Range(a)
If WorksheetFunction.CountIf(cel, "[COLOR=#ff0000]x[/COLOR]") > 0 Then
msg = msg & vbCr & a & " HIT"
Exit For
End If
With Range(a)
If cel.Address = .Cells(.Count).Address Then msg = msg & vbCr & a & " MISS"
End With
Next cel
Next a
MsgBox msg
End Sub``````

Returns a single message box detailing status for all ranges
Asuumes that "x" is a consistent value

Last edited:

#### Yongle

##### Well-known Member
or
If one HIT is enough, and you do not care where...
Code:
``````Sub Messages2()
Dim a, cel As Range, msg As String
For Each a In Array("A3:A5", "K2:N2", "K10:K14")
For Each cel In Range(a)
If WorksheetFunction.CountIf(cel, "x") > 0 Then
msg = "HIT"
GoTo msg
End If
Next cel
Next a
msg = "MISS"
msg:
MsgBox msg
End Sub``````

Asuumes that "x" is a consistent value

You requested that a cell "contains X", hence
Code:
`` If WorksheetFunction.CountIf(cel, "x") > 0 Then``
For "equals x" use
Code:
`` If cel.value = "x" Then``
(applies to both solutions)

Last edited:

##### Active Member
Thanks for this, its nearly perfect but i literally just want the message box to show 'Hit' or 'Miss' and nothing else. So if A3 has an X show 'Hit, if A4 has an X show 'Hit, ect if not show Miss. At the moment the message box shows:

A3:A5 HIT
K2:N2 MISS
K10:K14 MISS

##### Active Member

Sorry i should also metion that all of my data sits within range a1 to am28. So essentially if an X is within "A3:A5", "K2:N2", "K10:K14" then show HIT, else if an X is within any other cell within A1 to AM28 then show miss

#### Yongle

##### Well-known Member
Is there only one "X" in A1 to AM28?

##### Active Member

no, the x could be anywhere within the range but if its specially in the range "A3:A5" or "K2:N2" or "K10:K14" then the msg should show Hit but if someone enters an x anywhere else in the range, it should show miss. I'm basically creating the board game battleships within excel as a competition for staff. No worries if this can not be done its a nice to have and not essential.

#### Yongle

##### Well-known Member
Our posts crossed
I think code in post3 will return what you want - let me know if it doesn't

##### Active Member
thanks so i'm using the below code and the message shows hit if there is an x in range "A3:A5" or "K2:N2" or "K10:K14" but also shows HIT if there is an X in any other cell rather than miss

Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)

Dim a, cel As Range, msg As String
For Each a In Array("A3:A5", "K2:N2", "K10:K14")
For Each cel In Range(a)
If cel.Value = "x" Then
msg = "HIT"
GoTo msg
End If
Next cel
Next a
msg = "MISS"
msg:
MsgBox msg
End Sub``````

#### Yongle

##### Well-known Member
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, msg As String:       msg = "MISS"

If Not Intersect(Target, Range("A1:AM28")) Is Nothing Then
If UCase(Target.Value) <> "X" Then Exit Sub
For Each a In Array("A3:A5", "K2:N2", "K10:K14")
If Not Intersect(Target, Range(a)) Is Nothing Then msg = "HIT"
Next a
MsgBox msg
End If
End Sub``````

Last edited:

Replies
5
Views
115
Replies
3
Views
79
Replies
1
Views
89
Replies
10
Views
70
Replies
3
Views
45

1,109,336
Messages
5,528,104
Members
409,802
Latest member
joeino

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...