Message Box if cells contain criteria

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
307
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
 

Some videos you may like

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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
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:

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
307
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
 

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
307

ADVERTISEMENT

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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
Is there only one "X" in A1 to AM28?
 

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
307

ADVERTISEMENT

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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
Our posts crossed
I think code in post3 will return what you want - let me know if it doesn't
 

thedeadzeds

Active Member
Joined
Aug 16, 2011
Messages
307
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
Joined
Mar 11, 2015
Messages
6,770
Office Version
  1. 365
Platform
  1. Windows
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:

Watch MrExcel Video

Forum statistics

Threads
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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top