Warning Message if Cells Match

[austicks84]

Hi i would like to have a warning message box pop up if i enter a value into a cell that matches a value from another range of cells.

The range of cells with preset values in it is P3:P10

If i enter a value in the range R3:AC10 that matches one of the values in the range P3:P10 i would like the warning message box to pop up.

 

[etaf]

you could use data validation on that range R3:AC10 and use a countif() formula to check the range P3 & P10

https://www.contextures.com/xlDataVal04.html

countif($P$3:$P$10, R3 )

 

[ravisingh]

Create Names as under
P3:P10 - "ChkRng"
R3:AC10 - "InputRng"
The code below presumes both ranges are in the same worksheet
Paste the following code in the worksheet module
You are good to go

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, Chk As Range
    Set Rng = Range("InputRng")
    Set Chk = Range("CheckRng")
    If Intersect(Target, Rng) Then
        Mtch = 0
        On Error Resume Next
        Mtch = WorksheetFunction.Match(Target.Value, Chk, 0)
        On Error GoTo 0
        If Mtch > 0 Then MsgBox ("Found")
    End If
End Sub

 

[austicks84]

I have just tried this, however, the error message box comes up if the value entered into the range R3:AC10 does not appear in the range P3:P10. I would like the error message to come up if the value entered does appear in the range P3:P10. Is there a way to do this using data validation?

 

[austicks84]

I have just tried this, however, the error message box comes up if the value entered into the range R3:AC10 does not appear in the range P3:P10. I would like the error message to come up if the value entered does appear in the range P3:P10. Is there a way to do this using data validation?

 

[etaf]

countif($P$3:$P$10, R3 )>0

 

[ravisingh]

I have just tried this, however, the error message box comes up if the value entered into the range R3:AC10 does not appear in the range P3:P10. I would like the error message to come up if the value entered does appear in the range P3:P10. Is there a way to do this using data validation?

I checked the code on dummy data b4 posting....it does throw up an alert only when the entered data is found in P3:P10....if not the code performs no action.
The code assumes that both the input range and the check range are on the same sheet
Please post a screenshot of your data.

 

[ravisingh]

I have just tried this, however, the error message box comes up if the value entered into the range R3:AC10 does not appear in the range P3:P10. I would like the error message to come up if the value entered does appear in the range P3:P10. Is there a way to do this using data validation?

Name the ranges as suggested.
Select InputRng with cell R3 active
Do Data>Validation>Formula in the Settings tab
In the formula box enter:
=ISNA(MATCH(R3,CheckRng,0))
Put a custom error msg in the Error msg tab.
You are done.