View Full Version : Solved: Alert Message and highlight duplicate
U_Shrestha
03-18-2008, 12:05 PM
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
03-18-2008, 12:27 PM
Can you not just do it in access against a matched query?
U_Shrestha
03-18-2008, 12:35 PM
No, I have an Excel Workbook for data entry.
grichey
03-18-2008, 03:12 PM
Is there a lot of possible combinations for sub item or only those shown in the table?
grichey
03-18-2008, 03:24 PM
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
03-18-2008, 06:31 PM
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
03-19-2008, 08:35 AM
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
03-19-2008, 09:20 AM
Ok, I'll try that thanks "grichey".
grichey
03-19-2008, 09:23 AM
Tennessee eh? I'm moving up there in a month.
-Gavin
coliervile
03-19-2008, 09:26 AM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.