Rolling duplicate columns

[HeRoseInThree]

Every couple of days I add data to column A. After I do, I insert another column A. I would like to see, if at any time, i have duplicate data in 3 consecutive columns.

 

[Joe4]

OK, for this I opted to use the Pop Up message instead of highlighting (if we need to highlight, that will involve more code to find exactly where these values occur).

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim val As Variant

'   Exit sub if multiple cells updated at once
    If Target.CountLarge > 1 Then Exit Sub
    
'   Exit sub if update is not in 1st column
    If Target.Column > 1 Then Exit Sub
    
'   Check to see if entry is equal to values in new two columns in same row
    If Target <> "" Then
        val = Target.Value
'       See if value is found in column B
        If Application.WorksheetFunction.CountIf(Columns("B:B"), val) > 0 Then
'           See if value is found in column C
            If Application.WorksheetFunction.CountIf(Columns("C:C"), val) > 0 Then
'               Pop up message box alerting user
                MsgBox val & " is found in columns A, B, and C", vbOKOnly
            End If
        End If
    End If

End Sub

 

[HeRoseInThree]

That will work, nicely! THANKS!

 

[Joe4]

You are welcome.

 

[HeRoseInThree]

Building on this. If I add a new column A, that changes all of my other formulas. Is there a way to add a new column (every 2 days) and have it add the new column in the formula?

Example. I have in M2 a formula: =COUNTIF(A$2:C$30,L2)
But, when I add a new column A, the new formula will be =COUNTIF(B$2:D$30,M2)

 

[Joe4]

Building on this. If I add a new column A, that changes all of my other formulas. Is there a way to add a new column (every 2 days) and have it add the new column in the formula?

Example. I have in M2 a formula: =COUNTIF(A$2:C$30,L2)
But, when I add a new column A, the new formula will be =COUNTIF(B$2:D$30,M2)

This sounds like a whole different question that probably warrants its own thread.

 

[HeRoseInThree]

New thread created. Sorry.

 

[Joe4]

New thread created. Sorry.

No worries!