Conditionally formatting duplicate values across all versions of MS Excel is quite simple. However, to colour different duplicate values in different colours, refer to this workbook.
So I would love to use this code, but how do you edit what cells are colored. I need names colored in my sheet not A1,B2.
Thanks,
-
Hi,
I do not understand your question. The entries in the Name column can be changed to whatever you want.
Please clarify your question. To better understand your requirement, upload your file to OneDrive and share the link of the uploaded file here.
So you just say look at the workbook to see how it works. I did and the workbook says just use this macro: Sub different_colour()
Dim lrow As Integer
lrow = Worksheets(“sheet1”).Range(“B2”).CurrentRegion.Rows.Count – 1 + 2
For N = 3 To lrow
If Application.WorksheetFunction.CountIf(Worksheets(“sheet1”).Range(“B3:B” & lrow), Worksheets(“sheet1”).Range(“B” & N)) = 1 Then
GoTo skip
Else
Worksheets(“sheet1”).Range(“B” & N).Interior.ColorIndex = Application.WorksheetFunction.Match(Worksheets(“sheet1”).Range(“B” & N), Worksheets(“sheet1”).Range(“B3:B” & lrow), 0) + 2
End If
skip: Next N
Worksheets(“sheet1”).Activate
Range(“B3”).Select
End Sub
How do i edit that macro to color the cells that I want.
-
Hi,
So which specific cells do you want to colour. It will be best to share your data file – upload it to OneDrive and share the link of the file here.
-
That is the problem I want to add the macro to all my excel files to highlight certain names in every file.
-
Hi,
That macro will have to be run for each file individually.
-
Yes that is what I want can you help me edit your code so I can have this macro on each of my files?
-
Hi,
If my code is working fine for you on one file, then you can just copy the code form that file and paste it in the other file.
1. Press Alt+F11
2. Click on the module where the code is saved and then copy the code form the right hand side window
3. Press Alt+Q
4. Open the other file where you want to use this code and press Alt+F11
5. Go to Insert > Module
6. Paste the code on the right hand side window.The code will obviously work only as long as the data is in the same range as the other original file.
If this does not work, then you need to share far more details (along with the file).
-
Here is the link the problem is I need the North Houston Pole Line one color and the MP technologies LLC another color. The problem is not every excel file has all the names I need colored. Can you just teach me how to edit your code so I can have it color all the cells I have? Thanks for the help so far.
-
Hi,
when I open the file, I get a message saying that the file is corrupted. Furthermore, when it opens, there is no data in the file.
-
That’s weird it turns out my users no longer want this feature sorry, but thanks for all the help
-
-
-
Hi,
I like your template very much but i want one help how to use duplicate different values with different colours, like if you have different BOM in a sheet but some are repeated in different columns and rows so want to duplicate with different colours.
-
Hi,
Thank you. That is exactly what my solution does. Have you tried it? Also, I do not provide VBA solutions anymore so I will not be able to modify the VBA code to answer your specific question.
I know this is an old forum but I could really use some help here. I’m trying to apply this Module to multiple different columns at the same time instead of just one and having quite some difficulty. If your at all available Ashish Mathur I could really use a hand! Thanks
-
Hi,
I do not provide VBA support any longer so I would not be able to help. Sorry.
Assign different colours to different duplicate values in a range
{ 15 Comments }