Dec 02 2021 12:32 AM
Dear All
I hope you are having a great day
first, I would like to thank you all for your effort
here is the system info :
--------------------------------
I have this Button in a sheet containing the following code :
Sub ColorCompanyDuplicates()
'Updateby Extendoffice
Dim xRg As Range
Dim xTxt As String
Dim xCell As Range
Dim xChar As String
Dim xCellPre As Range
Dim xCIndex As Long
Dim xCol As Collection
Dim I As Long
On Error Resume Next
If ActiveWindow.RangeSelection.Count > 1 Then
xTxt = ActiveWindow.RangeSelection.AddressLocal
Else
xTxt = ActiveSheet.UsedRange.AddressLocal
End If
Set xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
xCIndex = 2
Set xCol = New Collection
For Each xCell In xRg
On Error Resume Next
xCol.Add xCell, xCell.Text
If Err.Number = 457 Then
xCIndex = xCIndex + 1
Set xCellPre = xCol(xCell.Text)
If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex
xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex
ElseIf Err.Number = 9 Then
MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel"
Exit Sub
End If
On Error GoTo 0
Next
End Sub
basically, it highlights the duplicate values in the table, what I want is to add 2 other stuff to do in the button:
Note: copy of the file attached :)
Thank you in advance
Dec 02 2021 03:38 AM
Check out the attached version.
Dec 02 2021 05:02 AM
thank you man for your response, but there is a small issue that needs to be fixed
Note: I attached the same file in your response
really appreciate your help, sir...
Dec 02 2021 06:45 AM
Which range do you select when you click the button?
Dec 05 2021 09:22 PM - edited Dec 05 2021 09:46 PM
the Range from columns ( i to n ), but still some points got highlighted ( A, E, F ) as the figure below :
if you could fix this, and the duplicate counting.. will be much appreciated
otherwise, it works really fine
Note: I attached the same file in your response
Thank you so much, sir
Dec 06 2021 05:21 AM
SolutionWhen I select I6:N27 and click the button, only cells in that range are colored. Perhaps the cells in columns A, E and F had already been colored before.
I have corrected the coloring and the count (I hope). See the attached version.
Dec 06 2021 11:51 PM - edited Dec 06 2021 11:54 PM
it's worked really well, just tiny issue is that the header of the columns disappears, if you could fix this, will be much appreciated.
thank you so much
Dec 07 2021 03:36 AM
Make sure that you select the range you want to format: I6:N27, not I5:N27 or an even larger range.
If you include I5:N5, the code will remove the background color of those cells, leaving the white text invisible.
Dec 19 2021 12:56 AM
Thank you so much, sir, really appreciate your hard work and effort in this matter
if you just do one last thing in this regard, will be much appreciated:
in the sheet report, I have button 1 that colors the duplicate after selecting the range
(( as you know :) )) .. could be possible if this step be automated ..... like once I click the button it selects the range directly without the pop-up message coming up.
the range will be fixed (non-changble), I want the range to be the whole columns ( i,j,m,n)
thank you again, you are the hero for me :)
Note: Copy of the file attached
Dec 19 2021 03:27 AM
See the attached version.
Dec 19 2021 03:55 AM
Dec 06 2021 05:21 AM
SolutionWhen I select I6:N27 and click the button, only cells in that range are colored. Perhaps the cells in columns A, E and F had already been colored before.
I have corrected the coloring and the count (I hope). See the attached version.