Message Box if cells contain criteria

[thedeadzeds]

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

 

[Yongle]

TRY:

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

 

[Yongle]

or
If one HIT is enough, and you do not care where...

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

 If WorksheetFunction.CountIf(cel, "x") > 0 Then

For "equals x" use

 If cel.value = "x" Then

(applies to both solutions)

 

[thedeadzeds]

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]

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]

Is there only one "X" in A1 to AM28?

 

[thedeadzeds]

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]

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

 

[thedeadzeds]

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

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]

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