Hi! Kindly help me on this problem. Im trying to do is when i input text on textbox and found duplicated on its reference column (column A) the textbox will highlighted with red and at the same time another textbox(Serve as status bar) will automatically wrote "DUPLICATED". Kindly see attached file. Thanks!
Textbox will highlight red when found duplicate cell value in column
-
Prospice -
August 8, 2017 at 9:39 AM -
Thread is marked as Resolved.
-
-
-
Re: Textbox will highlight red when found duplicate cell value in column
Hello,
Before getting into the UserForm .. and changing the background colors of your TextBoxes ...
you should ensure you have the proper code in order to identify your duplicates ...
Below is a macro to be tested :
Code
Display MoreSub FindDups() Dim rng As Range Dim c As Range With ActiveSheet.Range("A:A") Set rng = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) End With For Each c In rng If 1 < Application.CountIf(rng, c.Value) Then With c .Offset(0, 1) = "Duplicate" .EntireRow.Resize(1, .Column + 1).Interior.ColorIndex = 4 End With End If Next c End Sub
Hope this will help
-
Re: Textbox will highlight red when found duplicate cell value in column
Hi Carim,
Thank you for the response! Highly appreciated. This input will help me on my project but i need also need the userform with textbox that will response everytime i type same cell value on reference column. it is possible? thanks! -
Re: Textbox will highlight red when found duplicate cell value in column
Hello again,
What is your plan in terms of Serial Number input ...
Are you expecting the user to type them in ...?
-
Re: Textbox will highlight red when found duplicate cell value in column
Try
Code
Display MorePrivate Sub CommandButton1_Click() Dim bMatch As Boolean Dim vVal vVal = TextBox1 bMatch = WorksheetFunction.CountIf(Range("A2:A1000"), vVal) > 0 If bMatch Then TextBox1.BackColor = RGB(255, 0, 0) TextBox2.Value = "Duplicate" Exit Sub Else TextBox1.BackColor = RGB(255, 255, 255) TextBox2.Value = "Unique" lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Cells(lastRow + 1, "A") = TextBox1.Value End If End Sub
-
-
Re: Textbox will highlight red when found duplicate cell value in column
Quote from Carim;797500Hello again,
What is your plan in terms of Serial Number input ...
Are you expecting the user to type them in ...?
Hi Carim,
Yes! Exactly! Everytime the user will type the serial number and detected duplicate on reference column, it will highlighted and status bar will tell the user "DUPLICATED". Thank you for the time Carim.
-
Re: Textbox will highlight red when found duplicate cell value in column
Hi dotchiejack,
Thank you for this input! this is exactly what i needed. Thank you! i will mix the input of Carim and your input. Thanks!
:cheers: -
Re: Textbox will highlight red when found duplicate cell value in column
Glad you could fix your problem ... :wink:
Thanks a lot ... for your Thanks ... :smile: AND for the Beer ...:drunk:
-
Re: Textbox will highlight red when found duplicate cell value in column
Quote from dotchiejack;797508Try
Code
Display MorePrivate Sub CommandButton1_Click() Dim bMatch As Boolean Dim vVal vVal = TextBox1 bMatch = WorksheetFunction.CountIf(Range("A2:A1000"), vVal) > 0 If bMatch Then TextBox1.BackColor = RGB(255, 0, 0) TextBox2.Value = "Duplicate" Exit Sub Else TextBox1.BackColor = RGB(255, 255, 255) TextBox2.Value = "Unique" lastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Cells(lastRow + 1, "A") = TextBox1.Value End If End Sub
dotchiejack,
i have a question, what if i will compare my column A of sheet 1 and sheet 2 with same function as you gave to me. what code i will replace? Thanks!
-
Re: Textbox will highlight red when found duplicate cell value in column
Carim,
Can you help edit code given by dotchiejack? i need to compare column on different sheet (sheet 1 & sheet 2) with same function given by dotchiejack. Thanks!
-
-
Re: Textbox will highlight red when found duplicate cell value in column
Hello,
Why don't you attach the latest version of your workbook ... with what you have implemented so far ...:wink:
-
Re: Textbox will highlight red when found duplicate cell value in column
Hi Carim,
hehe your right! Sorry! Please see attached file. Thank you!
-
Re: Textbox will highlight red when found duplicate cell value in column
Attached is your revised workbook ... to be tested ...
Hope this will help
-
Re: Textbox will highlight red when found duplicate cell value in column
Hi Carim,
Exactly as i needed! Thank you so much for the time and effort on helping me! God bless!!!!
:dance: -
Re: Textbox will highlight red when found duplicate cell value in column
Glad you could fix your problem ...:wink:
Thanks for ... your Thanks ..:smile:
-
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!