check two cells for the same value

[Jockster]

One of the cells on the same row in columns "L" and "M" can have a tick in it (capital 'P', Wingdings 2 font).
Both can't have a tick in them. Shoulda second tick be sdded by a user, can a message appear warning them of the error and remove the last tick that was applied?

 

[mikerickson]

You could use Validation with the formula =(COUNTIF($L1:$M1,"P")<2)

 

[T. Valko]

One of the cells on the same row in columns "L" and "M" can have a tick in it (capital 'P', Wingdings 2 font).
Both can't have a tick in them. Shoulda second tick be sdded by a user, can a message appear warning them of the error and remove the last tick that was applied?

Maybe in column N you can put this formula:

=IF(COUNTA(L2:M2)>1,"Too Many Ticks","")

 

[Jockster]

Thanks guys, however, I wanted to avoid the formula or validation route. Ideally, I'd like a message box to relay the error message rather than a formula in a cell.

 

[mikerickson]

What is the problem with validation?
The process of validating user entry via VBA is inferior to Excel's Validation feature.

 

[Jockster]

I was thinking that the vba approach would be better because it can remove the rogue value from the cell therefore forcing the user to think about what he/she is doing whereas the validation route will warn them but they can leave the value in the cell.

 

[mikerickson]

Excel's Data Validation prevents the entry of the rogue value into the cell. Removal is not needed, since it never is there.
By the way, are you using a Double-Click to un/check the cells, if so, Validation is not triggered and a VB routine like

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Cells.Count = 1 And (.Column = 12 Or .Column = 13) Then
            If 1 < Application.CountIf(.EntireRow.Range("L1:M1"), "P") Then
                Application.EnableEvents = False
                MsgBox "only check one"
                .Value = vbNullString
            End If
        End If
    End With
    Application.EnableEvents = True
End Sub

is required.

 

[Jockster]

Thanks Mike.
Yes, I am using a double click to check the cells and this is where the validation fell down because, although it did warn the user, it left the value in the cell.
Thanks again.