Conditional Format With Message

[ayazgreat]

Hi

I am using following formula for duplicated numbers from range A3 to A15000(=Countif($A$3:$A$15000,A3)>1)

I want with this conditional format that when any number is duplicated it gives me a message the following number is duplicated and take me above of first duplicated number.

Thanks in advance

 

[Andrew Poulsom]

Sorry, you can't do that with conditional formatting.

 

[ayazgreat]

Thanks Sir for your Reply

So what would be the other way to do it if not to be done by condional format? Sir what do you suggest for it ? One thing I must figure out that i do not wish to prevent duplication I just want only intimation for duplication and take the first duplicated number asking that if I wish to go the same.

 

[ayazgreat]

Is there any solution for it?

 

[mikerickson]

You can use Data Validation to show a message when a duplicate value is entered. Conditional formatting could be set to highlight those duplicates, but moving to the cell with the duplicated values or changing the alert message depending on the duplicate, requires VB.

Setting A3 with

The Validation formula =(1=COUNTIF($A$3:$A$15000,A3)), error message type Information

and conditional formatting to =(1 < COUNTIF($A$3:$A$15000,A3)) will do that.

 

[ayazgreat]

Thanks Sir for your reply

I want VB codes for the same in last post.

Thanks ina vance

 

[mikerickson]

Putting this in the sheet's code module will alert the user whenever a duplicate entry is made in A3:A15000. The Active Cell will be changed from the new entry location to the location of the duplicated entry.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim hereCell As Range
    Set hereCell = ActiveCell
    With Range("A3:A15000")
        If Not (Application.Intersect(Target, .Cells) Is Nothing) And Target.Cells.Count = 1 Then
        
            .Find(What:=Target.Value, After:=Target, LookIn:=xlValues, LookAt:=xlWhole, _
                SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
            
            If Target.Address <> ActiveCell.Address Then
                MsgBox Target.Value & " is duplicated in cells " & ActiveCell.Address & " & " & Target.Address
            Else
                hereCell.Select
            End If
            
        End If
    End With
End Sub

 

[ayazgreat]

Thank you very much it works great