Highlight / warn when a value is entered again in the same row?

[rjn008]

Hello there, I am a newbie. I am a good excel user but not a good VBA or any language writer/programmer.

I want to create a VB script so that the below when entered into the cell on the same row, if it already exists to highlight this and a pop up warning if possible, to either accept and continue so it highlights or to have the option of not accepting the entry? Is this possible?

So on row 5 where Orange has been entered for a 2nd time, this is when I would like the VB to kick in, it could be on any row and any column? I use a data validation list view on each cell from the cells B20:B30 - can obviously vary, smaller or longer.

This is the mini sheet for you to help with;

Book1.xlsx
	A	B	C	D
1	Test	21/04/2023	25/04/2023	29/04/2023
2
3	7	7	7	1
4	Name	Week 1	Week 2	Week 3
5	A	White	Orange	Orange
6	B	Orange	Blue
7	C	Yellow	Red
8	D	Blue	Black
9	E	Red	Black
10	F	Purple	Pink
11	G	Black	Yellow
12
13
14
15
16
17
18
19
20		White
21		Orange
22		Yellow
23		Blue
24		Red
25		Purple
26		Black
27		Pink
28		Green
29		Violet
30		Plum
Sheet1

Cell Formulas
Range	Formula
A3	A3	=COUNTA(A5:A21)
B3:D3	B3	=COUNTA(B5:B11)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:H11	Expression	=ROW(A1)=CurrentRow	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B5:D11	List	=$B$20:$B$30

 

[Snakehips]

Morning @rjn008 In essence, yes.
However, this is code for the sheet change event and there can only be one set of code per event type.
So you will need that code to deal with both options, row or column. There are various ways you can do this and it may depend upon your need to separate out a row duplicate from a column duplicate? The below is not tested but should catch either row and/or column duplicates and highlight either but without saying which it is. It would be simple enough to have it message 'Column Dup' or 'Row Dup' if it is necessary.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim RowRng As Range
Dim ColRng As Range

'Dont apply code if it is a change to more than just a single cell
If Target.Cells.Count > 1 Then Exit Sub

'determine changed cell ,'Target', is within sensible range of interest
If Not Intersect(Target, Range("B3:AZ15")) Is Nothing Then  '<< ????? Edit range to suit


    Set RowRng = Range(Target.EntireRow.Address)   ' set to Target row
    Set ColRng = Range(Target.EntireColumn.Address)   ' set to Target Column
    
    ' check for duplicate entry in row or column
   Debug.Print Application.WorksheetFunction.CountIf(RowRng, Target) > 1 Or Application.WorksheetFunction.CountIf(ColRng, Target) > 1
    
    If Application.WorksheetFunction.CountIf(RowRng, Target) > 1 Or Application.WorksheetFunction.CountIf(ColRng, Target) > 1 Then
           
                Target.Interior.ColorIndex = 3  'if duplicate, hidhlight CELL red
         'display message andawait response
       Resp = MsgBox("Do you wish to keep this duplicate enty?", vbYesNo, "DUPLICATE ENTRY!!!")
                
               
               If Not Resp = vbYes Then   'If response is No then
                Application.EnableEvents = False  'Disable event handling otherwise this event will  call itself again
                
                    Target.ClearContents   'clear the Target entry
                    Target.Interior.ColorIndex = xlColorIndexNone 'clear the highlight
                     Target.Select  're-select the Target cell forpossible re-entry
                     
                  Application.EnableEvents = True  're-establish event handling
                End If

      'If response is Yes then do nothing and the duplicate cell will remain highlighted
            
    End If
End If
End Sub

 

[rjn008]

Hi again @Snakehips, I'll give that a go however, my apologies for not being specific, it is just another sheet I am using where by it would just be a list in a column rather than a row, so can you do a script just for a column event?

 

[rjn008]

Oh, and again I would like to highlight the cell not the column upon error?

 

[Snakehips]

If it's for a separate sheet, dedicated to find row duplicates then do as you suggested.
Copy your column based code, paste it into new sheet code pane.
Edit the Set Rig = line to be .....

Set Rng = Range(Target.EntireRow.Address)   'Rng set to Target row

 

[rjn008]

Thank you mate... I'll call this complete now