Solved: Alert Message and highlight duplicate

[U_Shrestha]

Hello all,

In the attached spreadsheet I am looking for two types of alert.

a) If any cell a2:a19 is left out then an alert message saying "Please enter data in "--" cell". Actually, the range a2:a19 is a dynamic range.

b) Background: Values in Column A and Column B can be Duplicate, but values in Column C (Sub-item No.) cannot be duplicate corresponding to its Item No. in Column B.

If a sub-item no. is repeated in column c then corresponding rows should be highlighted just like in the attached sheet with a message saying "You have duplicate entry in cell " " and " " !" (Cell address is optional but red color highlight is really needed to find the duplicates.

Is this possible?

[grichey]

Can you not just do it in access against a matched query?

[U_Shrestha]

No, I have an Excel Workbook for data entry.

[grichey]

Is there a lot of possible combinations for sub item or only those shown in the table?

[grichey]

Via google. Test for column A & B & C.
Sub TestForDups()

Dim LLoop As Integer
Dim LTestLoop As Integer
Dim LClearRange As String
Dim Lrows As Integer
Dim LRange As String
Dim LChangedValue As String
Dim LTestValue As String


'Test first 200 rows in spreadsheet for uniqueness
Lrows = 200
LLoop = 2
'Clear all flags
LClearRange = "D2:D" & Lrows
Range(LClearRange).Interior.ColorIndex = xlNone
'Check first 200 rows in spreadsheet
While LLoop <= Lrows
LChangedValue = "D" & CStr(LLoop)
If Len(Range(LChangedValue).Value) > 0 Then
'Test each value for uniqueness
LTestLoop = 2
While LTestLoop <= Lrows
If LLoop <> LTestLoop Then
LTestValue = "D" & CStr(LTestLoop)
'Value has been duplicated in another cell
If Range(LChangedValue).Value = Range(LTestValue).Value Then
'Set the background color to red
Range(LChangedValue).Interior.ColorIndex = 3
Range(LTestValue).Interior.ColorIndex = 3
End If
End If
LTestLoop = LTestLoop + 1
Wend
End If
LLoop = LLoop + 1
Wend
End Sub

[coliervile]

I ran your coding in the above workbook and didn't get anny chang. I also copied the worksheet to another workbook without any colors on the worksheet and the cells didn't change colors that were dupes???

[grichey]

This code references column D. You need to add a column to the original data to come up with indexes. For it to highlight, set column d1 (for example) as =value(A1&B1&C1) which gives you the unique values to test against.

[coliervile]

Ok, I'll try that thanks "grichey".

[grichey]

Tennessee eh? I'm moving up there in a month.

-Gavin

[coliervile]

Gavin, my home sits at the base of the Smoky Mountains in Pigeon Forge, Tennessee...it's absolutely gorgeous there. I'm currently in Michigan, but retirement is only a couple of years away and it's back to Tennessee.