Free Microsoft Excel 2013 Quick Reference

Color Code Duplicate items.

Please help! I have a spread sheet that is massive. I have one column with a
million numbers in it. I need to color code it each time it shows up. The
first instance should be white, the second should be yellow, and the third
time it shows up it should be red. Is this possible? I have researched all
morning long. Please help! Thank you so much!

Post your answer or comment

comments powered by Disqus
I would like your opinion on how to highlight duplicates in such a manner that all cells containing the same string will be color coded in the same manner.
I included a sample file.
Conditional formatting as the different strings are over a thousand is not really a solution.
the color assigned isn't important, it could be random, as long as not two different strings will share the same color.
I hope this is clear enough.
Thank you in advance.

PS, the sample here is only a small scale, it would eventually be much larger (up to 30 columns and 100 rows)

Dear all

I observed there is a lot of forum question related to row delete with duplicate item or empty.

I just wondering is that a VBA code that can be used to delete the rows that have non-duplicate item

This code below is great. But there is one thing I cant figuer out how to exclude certain color codes. The code searches thru the worksheet and if it find duplicates it will color the text in a different color. Now There are some colors I dont want, Say yellow it does not work with a white back ground. In the code it has this:

If i


I have Column A filled with dates for which a part was built. There are multiple builds in a day. If anyone could help me with some VBA code, I would like to:

-Sort Column A by date
-Count duplicate items in column A
-Output the Date in a new sheet along with the count for that date.
-Average the total counts that are on the new sheet.

In the end, i'm looking for average builds per day and I need to leave the original sheet unchanged.

This is my second question for the morning, any help with either is much appreciated. Here is a link to my other question.

Thanks in advance!

Hi all,

As I mentioned in another thread, I'm working on a form which will be sent by email from Excel. I'm very happy with the layout of the form at the moment, and when it sends it looks great on the recipients end.

For reference, I'm using some option buttons (from the Excel Forms toolbar), some group boxes (just to make it look nice) and mainly bordered cells that the user fills in. (Sheet will be protected when I'm done). From what I can see, all the option buttons and group boxes are converted to pictures before sending. Doesn't bother me. It's just a display.

Now, I mention that the form looks great in email. It does when the user first opens the email. BUT, if they try to forward the email, Outlook creates duplicate items of the option buttons and group boxes. So what I'm left with is option buttons, sitting on top of pictures of option buttons.

I've attached the code I'm using to send the form, in case it assists with working out what is going on.

Sub Button10_Click()
    Dim Sendrng As Range
    Dim Claimnum
    Claimnum = Cells(8, 4).Value

    On Error GoTo StopMacro

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Note: if the selection is one cell it will send the whole worksheet
    Set Sendrng = Selection

    'Create the mail and send it
    With Sendrng

        ActiveWorkbook.EnvelopeVisible = True
        With .Parent.MailEnvelope

            ' Set the optional introduction field thats adds
            ' some header text to the email body.
            .Introduction = "This is an assessment request for " & Claimnum

            ' In the "With .Item" part you can add more options
            ' See the tips on this Outlook example page.
            With .Item
                .To = Cells(6, 2).Value
                '.ReplyRecipients.Add ""
                .Subject = "Assessment request for " & Claimnum
                While .Attachments.Count > 0 'Remove previous attachments
                .Attachments.Remove 1

                .Attachments.Add "h:Landlords AR Template.doc"
                .Attachments.Add "h:Landlords Invoice Template.xls"

                ''Attempt to send without user intervention.
                ''Doesn't really work...
                'Application.Wait (Now + TimeValue("0:00:02"))
                'Application.SendKeys "%S"
            End With

        End With
    End With

''Enable below for error numbers
'MsgBox Err.Number & Err.Description

'Cleanup code
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    ActiveWorkbook.EnvelopeVisible = False

End Sub
Thanks for your time, it's greatly appreciated.

Edit: P.S. I'm happy to try sending the email form to you if it helps show what's happening. As an FYI, tests have so far been in Outlook 2003. I'm going to send it to myself at home later to see how it displays.


First time visitor, first time poster...

I have 3 lists of names. Names within each list may have duplicates, and names among all the lists may have duplicates too. I've combined the 3 lists and color coded the cells to differentiate which list each name came from. I then sorted the 3 lists in alpha order so all lists are intermingled.

My goal is to highlight the duplicate names if and only if they appear in more than one list, but not if they appear in only one list. For example, if John Drake appears twice in list A, then don't highlight, but if he appears in list A and B, then highlight. I've figured out how to highlight the name if it has a duplicate (regardless of cell color); however, is there a way to include cell color as a conditional factor so that it only highlights if the cell colors are different?

Thanks in advance,


I have a dynamic list of 2 columns wide in alphabetical order(all items are
text). The first column contains many different duplicate items (these are
together as its in alpha order), the second column's items are all unique. I
wish to delete the first of each of the duplicate items and close all gaps in
order to reduce the length of the list. I am using Excel 2002.

I have a several sheet document that has an index with hyperlinks to 300 different selections. What I wanted to add is a data validation list that if a specific selection was made, such as yes or no it would color code the row or cells on the associated sheet. Has anyone know how to accomplished that and if so did you use Macros or VB? Thanks for any help.

-Edit- I added the Spreadsheet as an attachment. I need the id to be referenced on the CO sheet to color code. Basically Sheet 2 is the reference, Index is the index and CO is one of my sheets I need color coded. Example: if I select GPO from the Function tab I want it to reference the group ID (V-1076) and know that A2:J2 would be Green, if I select Manual A2:J2 Brown, If I select Patch A2:J2 Blue.

It keeps error-ing out with my file upload I can send it to you if you could help...Thanks


I have a sheet which contains 111 counterparties which have four rows per record. I call this MASTER. An example would be:

LSE999 TEST 3043 0001

I then have a second sheet with some command buttons. One of those is Paste which gets data off an exchange website and pastes it into this second sheet. From there i have another button which compares this second sheets values against the master sheet in a for loop and outputs any changes in an adjacent column with the same row number it finds in a color coded fashion. An example would be:

TEeeeeeST 3043 0001

The only thing I cannot seem to do right now is put it in a nice order at the top of the sheet. I believe I might need an array to do this, whereby I store any results and then use a second for loop to put those results out in order. So I would like it to put each counterparty it finds to have changed / is new into J1, then J2 and so on. Currently it puts the results in J13 then J29 for example.

Currently I am only searching the first two parts of the record for ease of testing, I will be expanding this to all four and perhaps seven rows across.

Dim i As Long 
Dim X As Long 
X = 0 
Range("A:I").NumberFormat = "@" 
 'For loop to test the pasted counterparties and compare them against the master.
For i = 1 To Sheet6.Range("A" & Rows.Count).End(xlUp).Row 
     'Increase Row number by one
    X = X + 1 
     'If statements to find a difference between both sheets and write the values in adjacent column
    If Sheet6.Range("A" & i)  Sheet1.Range("A" & i) Then 
        Sheet6.Range("F" & X).Cells.Interior.ColorIndex = 42 
        Sheet6.Range("F" & X).Value = Sheet6.Range("A" & i).Value 
        Sheet6.Range("G" & X).Value = Sheet6.Range("B" & i).Value 
        Sheet6.Range("H" & X).Value = Sheet6.Range("C" & i).Value 
        Sheet6.Range("I" & X).Value = Sheet6.Range("D" & i).Value 
         'X = X + 1
    End If 
    If Sheet6.Range("B" & i)  Sheet1.Range("B" & i) Then 
        Sheet6.Range("G" & X).Cells.Interior.ColorIndex = 37 
        Sheet6.Range("F" & X).Value = Sheet6.Range("A" & i).Value 
        Sheet6.Range("G" & X).Value = Sheet6.Range("B" & i).Value 
        Sheet6.Range("H" & X).Value = Sheet6.Range("C" & i).Value 
        Sheet6.Range("I" & X).Value = Sheet6.Range("D" & i).Value 
         'X = X + 1
    End If 
If Sheet6.Range("F2") = 0 Then 
    M1 = MsgBox("Nothing To Change", vbOKOnly + vbInformation, "Borsa Italiana Check") 
End If 

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

In my previous organization I, with the help of my Manager, created a calendar wherein I used "Color Code" to mark the leaves of the employees for a calendar year.

The function of the tracker was to record when people have taken leaves, when have they booked the leave the leaves due to them.

I chose color "Red" for Leaves Taken, "Blue" for Half Day and "Green" for Leaves Planned. So, whenever in Excel Calendar I color the cell representing 5th July (example) RED, 1 day is automatically deducted from the employees' Total Leave Entitlement.

Thanks to my really Bad Memory (!!!!) I am unable to recall the code and unfortunately cant take the same from my previous manager.

Can any of you please help me with it??


While creating a chart, I wish to give a different color code to each series collection. How to do this? As of now, I am using "Chart.ChartStyle" which gives a single color(one dark and one light) to both the series. Here is the code snippet:

myChtObj.Chart.ChartType = xlBubble3DEffect 
myChtObj.Chart.SeriesCollection(1).Name = "=""Series 1""" 
myChtObj.Chart.SeriesCollection(1).XValues = _ 
myChtObj.Chart.SeriesCollection(1).BubbleSizes = _ 
myChtObj.Chart.SeriesCollection(1).Values = _ 
myChtObj.Chart.SeriesCollection(2).Name = "=""Series 2""" 
myChtObj.Chart.SeriesCollection(2).XValues = _ 
myChtObj.Chart.SeriesCollection(2).BubbleSizes = _ 
myChtObj.Chart.SeriesCollection(2).Values = _ 
myChtObj.Chart.ChartStyle = 8 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This gives light and dark Orange color. How do I give Series1 with orange and Series2 with maybe green?

I Need A Macro That Will Run When A Duplicate Item Number Appears In The Same Column. So For Instance If Aaa Exists In Column "b" And Someone Trys To Enter Aaa Again A Macro Will Run.

Thanks In Advance -

I am trying to color code the cubes in my chart. I'd like to be able to be able to specify a specific color per cube. What code what I write for the Sheet to do this? Thanks.

Is there a formula that I can use to color code cells? Ex: I want to color code everything that is less than 10.

I realize that I can just go through and do it myself but there is just far too much data and it will take me forever.

color coding employess... dave h.

you were gracious enough to assist me with this--it works ok when the named range "employees" is in the same tab (excel 2003), but it doesn't pick up the color when i move the "employees" range to another tab (which i would like to do...)...? thank you.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next

Dim isname
If Not Intersect(Target, Cells) Is Nothing Then
isname = Application.Match(Target, Range("employees"), 0)
If IsNumeric(isname) Then _
Target.Font.ColorIndex = _
End If
End Sub


I have a form which cycles though rows showing the content of several columns.
One of this columns contains text and I would need to display this in a textbox and highlight certain keywords in different colors.

As a silly example, if my keywords were 'Bob Dylan', 'Bon Jovi' and 'Frank Sinatra', my text might look like:

Now I love Bob Dylan music, but until 10 years ago I didn't know most of his songs and was much more fond of Bon Jovi. My dad loves Frank Sinatra and thinks all other music is utter rubbish.

1) Is there a way of using more than one font color in the same text field within a form?

If that's not the case, as I think, can anybody suggest any alternative solution for displaying the color coded text or at least putting in bold the keywords?

I was thinking of creating an html file on the fly and displaying it in the browser, but this would make the whole thing less immediate to use.

2) Also, is there a way to display a webpage within an excel form?



I am trying to get a column of dates to display a color code alert based on todays date. This is to provide a warning that todays date is two weeks prior to the date in the column and emails need to be sent out. Found some threads that seem to have the answer but I cannot make those work. Any help would be greatly appreciated.

Good afternoon. I searched through the files and found a macro that works for me to color code my spreadsheets in Excle. Hwoever. How can I set it to stop when Cloumn G = the word "Created" ? Any assistance wuld be helpful

Thank you

    Dim lRow As Range 
    For Each lRow In Range("A6:K500").Rows 
        Select  Case lRow.Row Mod 2 
    Case Is = 1: lRow.Interior.ColorIndex = 34 
    Case Else: lRow.Interior.ColorIndex = 35 
    End Select 
Next lRow 
End Sub 

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

I need to write a macro that automatically color codes my input upon entering the data in col C and col D. Each column will only have two characters eg. C 3. The first character must automatically appear in yellow and the second in cyan. The column background must be black. The first row in which the data is entered is Row 5. The font must be in bold and there must be a spacebetween the two characters.
The data will not be the same all the time...but the color coding will always be first yellow and second cyan.

Any help please

Hi all,

Looking for a code that checks for the changes in 2 sheets and color codes the changes in the same sheet. i,e. if there is any change in a cell it has to have a red color code and if no changes needs to have a green code.

Please advise if anyone has a solution to this.



Good day to all. I am wondering if you can Alpha sort merged cells , and if so, can you also create a form with merged cells to be able to easily add new entries. Also, I recieved the coding on how to color code a worksheet but need it to apply in a different way. - code was for everyother line odd,even . need colors to alternate every four columns, from 1 to 500. here is code that did every other line


Dim lRow As Range

For Each lRow In Range("A6:I500").Rows
Select Case lRow.Row Mod 2
Case Is = 1: lRow.Interior.ColorIndex = 34
Case Else: lRow.Interior.ColorIndex = 35
End Select
Next lRow

I have a spreadsheet with a row for a company name, their offer price underneath that, and a 1,2, or 3 underneath that. In a seperate tab I have the offer prices in order of rank with the company name next to the corresponding price using the Index function. I also need to be able to color code the background of the offer price based upon the 1,2 or 3 associated with each name. I am new to the Index stuff so I need some help putting this one together. Thank you in advance!

I want 3 levels of color coding in cells, but I have used h lookups and I'm pulling data from 4 different places. The data will be in two columns / 55 rows per company These are my formulas, if they help any...

=IF(HLOOKUP(ETR!I$4,'Company Structure'!$F$6:$EN$62,2,FALSE)=1,HLOOKUP(ETR!I$4,'Data Referenced'!D$5:AY$62,2,FALSE),IF(HLOOKUP(ETR!I$4,'Company Structure'!$F$6:$EN$62,2,FALSE)=2,$H7,0))

=IF(HLOOKUP(ETR!I$4,'Company Structure'!$F$6:$EN$62,43,FALSE)=3,G9*I9,F9*I9)

We will input "1", "2", or "3" into the company structure sheet. We will export % from a database into a 2nd sheet sheet and bring them together in a third sheet using formulas.

If the formulas in the summary sheet pulls a 1, it will then pull a % from a data sheet and the cell should be yellow. If the number pulled from the company sheet is 2, the formula will pull a single number input into column H of the summary sheet and the cell should be green. If the formula in column J returns number 3 it should multiply the number in column G by the formula in column I and the cell should be coral. Of course, in all instances...the formula doesn't really "pull" or "return" the using them as reference points are out.

I don't think conditional formatting will work for this and that I need a macro. I've searched through the past threads and tried a couple, but couldn't get them to work. Thanks for any help you can offer.

Does anyone have a macro to build a 'stop light' color coding system? For instance, if I have budget, actual, variance and goal columns, is there a way to automate the calculation of a fifth column (based on the goal column) with stop lighting for each row...

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