Free Microsoft Excel 2013 Quick Reference

mark duplicate cells with different color

I currently face very tricky problem about excel. Here is the problem:
I want to mark the duplicate cells in one colume with different color, and pick the corresponding individual cell IDs into another colume (like attached spreadsheet).

Maybe VBA can do that? it will be appreciated for any suggestions or answers. thanks advance for any kind friends.

Post your answer or comment

comments powered by Disqus

I'm familiar with how to Conditionally Format cells to highlight information when there's duplicate information.

What I'm wondering is if theres a way to highlight duplicate information in different colors. I have two columns, one with a description of issues, the second is product numbers.

I want excel to search for duplicate product numbers in the worksheet and group them by color, as opposed to simply highlighting ALL duplicates.

So that when I filter for Duplicates only, I can see them listed in different colors.

Attached is a small fraction of what my worksheet looks like, there are several more duplicate rows than what's shown, so manually doing this would be hard. In the file is what I have and then also what I want it to look like.

Hi I would like to cound different cells with different colors

Check attached file

Hi I'm new to the forum and new to programming for excel. I am trying to organize a spreadsheet by filling in certain cells with certain colors.

First I want to find all the cells under the column titled "Code" (currently column B if that makes it easier) that contain "TI" (thats a capital i) and then in each of these rows color in the column titled "TN_Required_End" (currently column L) with the color green.

Next I want to find all the cells under the column titled "Type" (column F) that contain "D2" or "B3" and then in each of these rows check the column titled "TN_Planned_End" (column N) to see if it contains any characters (specifically to check if there is a date entered there). If there is a date, leave it alone. If there is no date, color it yellow.

Let me know if I need to clarify anything. Thank you

I need a Marco to find duplicate cells In range A:A with range B:B and mark them with red color
For example : I created 2 files before.xls and after.xls , looking for duplicates names : first name and last name .
Thank you very much!

I want to use with this macro to bold duplicates instead mark them with red color
How do I do it?
Thank you
Sub Mark_Duplicate_with_red()
Dim a, i As Long, n As Long, t As Long, msg As String, txt As String
With Range("a1", Range("a" & Rows.Count).End(xlUp))
    a = .Value
    .Interior.ColorIndex = xlNone
End With
n = 2
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare  '<- delete this line if you want binary compare
    For i = 1 To UBound(a, 1)
        If Not .exists(a(i, 1)) Then
            .Add a(i, 1), 1
            txt = txt & "," & Cells(i, 1).Address(0, 0)
            If .Item(a(i, 1)) = 1 Then n = n + 1
            t = t + 1
            .Item(a(i, 1)) = 0
            If Len(txt) > 245 Then
                Range(Mid$(txt, 2)).Interior.Color = vbRed
                txt = ""
            End If
        End If
    If Len(txt) > 0 Then Range(Mid$(txt, 2)).Interior.Color = vbRed
    If n > 0 Then
        msg = .Count & " unique items" & vbLf & n & " item" & IIf(n > 1,
"s", "") & _
       " with dups" & vbLf & t & " dupls in total"
        msg = "No dup"
    End If
End With
MsgBox "Total recodrs scanned " & UBound(a, 1) & vbLf & msg
End Sub

Hi Everybody,
I need to know whether its possible to replace a word with different color. The word is found inside a cell in between other words.
When finding the word with Search option, the whole cell is selected instead of selecting the word alone. This is to be done in Excel Macro.
Looking forward for a good response.


is it possible to shade a cell (cells) with different colors than those present in the the Table with Conditional formatting/Format-Cells-Pattern-Shading ?
E.g. I want the cell(s) to have a lighter blue than the default Cyan (lowest row in the colour table).

Is it possible - how?

My cells would sit in say A4, A18, A33, A47 etc, etc (irregularly)


I have inherited a system where tools are tracked in a Gagetrak database, and also tracked in a company database.
I am trying to sort and mark from the attached example, all duplicate cells, and also somehow mark the cells with more than one entry since the data I get shows all locations, not just current.
I have used bold text to show the file copied from. I am not able to get what I need and I appreciate any help!
Thank you,

davegb Mar 9, 12:19 pm show options

Newsgroups: microsoft.public.excel
From: "davegb" <> - Find messages by this author
Date: 9 Mar 2005 12:19:22 -0800
Local: Wed, Mar 9 2005 12:19 pm
Subject: duplicate spreadsheets with different names
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

I'm creating spreadsheets for each county in the state (64). I've
created a master spreadsheet, with macros and buttons to make things
easier for state employees. Now, I need to make 63 copies of this
spreadsheet, one for each county, with the county name in a merged cell

in the upper left corner (where A1 would be) of each worksheet,
following other descriptive text. Each spreadsheet will be named by
county and put in a county folder.

To clarify, I need a macro to create 64 folders, one for each county (I

already know how to do that), then take a standard workbook, or
template, give it the county name along with other descriptive text,
put the county name in following other text in the first cell in each
sheet in the workbook, and place it in the appropriate county folder.

Of course, I'm going to set up and test this on my local hard drive
with about 5 "counties", and after it works there, change the path for
the folders and spreadsheets to go to, the server, and do the actual
creation of the user files there.

Can anyone help me with this? Thanks in advance!

Hello all, hope everyone is good?
I'm using some code right now to count the number of cells that have a certain color, that part is working fine. What I would like to try to figure is how to count the cells with same color but different values.
For instance I have 28 items to be deleted in total. I want to try and show exactly how many will be deleted per classification.
Total to be deleted: 28
Fixed: 0
I&E: 28
Rotating: 0
Can someone point me in the right direction to get this accomplished?

Sample included

thx in advance,

Hi fellows,

I have a question, can I fill a cell with a color in VBA? If is that possible, how can I do it?

Kind Rewards.


I need to average (or use any other function for that matter) on cells with different index. What I mean is, for example I need to average cells that are spaced 124 cells apart:
Let's say A15, A139, A263 etc.

Please help.

how can i select all the cells with same color on a sheet if there are
multipale colors by vba code

Is it possible to plot a scatterplot with different colors for 2 sets of data?

E.g Type of Agreement MRP Discount%

Limited 10 5%
UnLimited 20 10%

I want to show different colors for Limited & Unlimited.

Is it possible to plot scatterplots on both axis(Primary & Secondary)

Please let me know.



How can we set the interior color of a cell with custom colors other than
color index say i wan to set "#ccccff" this as background color
of cell.

Dear Friends,

I have two date columns, say E & F.

In these two columns I have entered date values. I need to do colour, font bold, Italic automatically when date values are entered in these columns - with a conditional formatting - i.e. every month should be in different color.

For example, values 23/05/2009, 15/10/2005, 07/12/2004 must be automatically coloured with different colors as its months are different.

Can any one please help me to solve this problem.

Thanking you,


Dear Wizzards,

How can I do either of the following without using a macro (in other words - with built in excel formulas):

1 - Display in a cell the celladdress of a cell containing the value "X", which is located within a one-row range of cells.

2 - display the contents of the only different cell located within a one-row range. In other words - the range of cells the formula needs to loop through are all on one row (that's what I meant above by one-row range as well). At any given time all, but one of the cells will show "FALSE". I'd like to display in a cell outside this range the contents of the one cell with different contents.



I am not getting coffe color in my excel color pallete. Could any one help me how can I fill a cell with coffee color ?


I'm currently constructing a worksheet which will be an aid in comparing properties for different models. Each model has a number of properties and I have a number of models that should be entered in the worksheet. What I want to achieve is an automatic marking of duplicates for each property.

The tricky thing is that I want the "most common" duplicate to be marked with one color, the "second most common" duplicate to be marked with another color, the "third most common" duplicate to be marked with yet another color and so on.

Properties that only occur once should be left unmarked. I would prefer to be able to do this with conditional formatting, but maybe it isn't possible? In either case, how can I achieve this functionality?

To further clarify what I'm asking for I attach a worksheet illustrating the end result that I would like excel to generate automatically as soon as the values are entered. Please note that the values can be numbers as well as strings.

Thank you in advance!

Best Regards,

Habitually I use a custom format cell for numburs with @.
So, When I choose @"/1242/2006", I can write only a number to get also the fix text directly. For example:
I write only 5 and the result will be 5/1242/2006
and to write 6 to get also 6/1242/2006
now,I hope to get the same result but in different colors for this text i.e @/1242/2006
I tried to use the conditional format cells, but I failed.
can you help me!

When I format cells with different colors, the grey gridlines disappear. I know I can get them back by clicking "No Fill" on the "Fill Color" icon, but is there a way to get them in colored cells? I've tried a grey border, but I don't want the grids to print.

I would like to add text to a cell with two different font sizes. I don't know the syntax to do this.

The following code places an employee name into a cell with his/her employee number, to the right, in parenthesis. The result is:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would like the result to be:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would be grateful for any help with this.

Set ACell=sheets("Active Employee Profile").Range("B2") 
sheets("Attendance Record").Range("A65536").End(xlUp).Offset(1).Value =  _ 
Application.WorksheetFunction.Proper(ACell.Value) & "(" & ACell.Offset(, -1).Value & ")"[/SIZE][/SIZE][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone,

Is there a way to highlight cells with a color when moving from cell to cell, the cells already have a border so when changing cells or doing a find, the cell border goes dimm and if doing lots of finds it's hard to see, I know the number of the row gets highlighted, is there a way to highlight the cell with a color, or highlight the border of the chosen cell with a different color?

any help is much appreciated.

I have a curve, I want to mark some part of this curve with a different color, how can i do that using excel?

for example, a curve as following, I want to mark the point from (4,4) to (6,6) with a different color.
x y
1 1
2 2
3 3
4 4
5 5
6 6



No luck finding an answer? You could always try Google.