by Allen Wyatt
(last updated May 19, 2016)
Connie has a worksheet that has company names in each cell of column B. They are grouped under a region heading (Northeast, West, etc.) in column A. She would like to apply conditional formatting to the company names so that if a name appears in more than one region, it shows up using a background or text color that makes the matching companies easy to find. This means that if one company is formatted as red, no other company should appear as red (it should appear as a different color, such as blue or green). Connie isn't sure how to set this up or if it can even be done with conditional formatting.
There is a way to mark duplicates using conditional formatting; just follow these general steps:
Figure 1. The New Formatting Rule dialog box.
At this point all your duplicates should match whatever formatting you selected in step 6. The only problem is that all duplicates are formatted the same way. In other words, if you have two companies (ABC Company and DEF Company) and there are duplicates for those companies, they are all formatted the same way—you won't see different formatting for the two companies.
Of course, you could easily use Excel's filtering capabilities to single out duplicate companies, non-duplicate companies, or individual company names. This might be the easiest way to "zero in" on the companies you want to locate.
The only way to use conditional formatting to apply different colors to different groups of duplicate company names requires that you identify, up front, the actual duplicates. With that list in hand, you could create a series of conditional formatting rules that use formulas similar to the following:
=AND(ISNUMBER(FIND("ABC Company",B1)),COUNTIF($B$1:$B$99,"ABC Company")>1)
In this formula "ABC Company" is the company name, B1 is the first cell of the range, and B1:B99 is the full range of cells. For each formatting rule you could apply different formatting appropriate to that particular company. That means that if you knew, up front, that there were 24 different company names that had duplicates, you would need to set up 24 conditional formatting rules to handle those 24 names.
Complex, indeed. Unfortunately, there is not an easier way using conditional formatting. You could, however, forego the conditional formatting and use a macro to make your duplicates stand out. The simplest "automatic" macro we could come up with (where you don't need to know the duplicate names ahead of time) is one that examines a range of cells and sets the internal cell color based on duplicate company names.
Sub ColorCompanyDuplicates() Dim x As Integer Dim y As Integer Dim lRows As Long Dim lColNum As Long Dim iColor As Integer Dim iDupes As Integer Dim bFlag As Boolean lRows = Selection.Rows.Count lColNum = Selection.Column iColor = 2 For x = 2 To lRows bFlag = False For y = 2 To x - 1 If Cells(y, lColNum) = Cells(x, lColNum) Then bFlag = True Exit For End If Next y If Not bFlag Then iDupes = 0 For y = x + 1 To lRows If Cells(y, lColNum) = Cells(x, lColNum) Then iDupes = iDupes + 1 End If Next y If iDupes > 0 Then iColor = iColor + 1 If iColor > 56 Then MsgBox "Too many duplicate companies!", vbCritical Exit Sub End If Cells(x, lColNum).Interior.ColorIndex = iColor For y = x + 1 To lRows If Cells(y, lColNum) = Cells(x, lColNum) Then Cells(y, lColNum).Interior.ColorIndex = iColor End If Next y End If End If Next x End Sub
To use the macro, simply select the cells that contain the company names and then run it. The macro makes three passes through the cells. The first pass looks backward through the cells from the current one being examined; it is used to determine if there are any "backward" duplicates, because if there are than no further processing on that particular cell is needed. The second pass looks forward through the cells to determine if there are any duplicates to the current company name. If there are, then a third pass increments the cell color value and then applies it to the duplicates.
Note that the macro sets the ColorIndex property of any duplicates it finds, and it increments the variable used to set the property when it finds a new set of duplicate company names. For all those company names for which there are no duplicates, the ColorIndex property of the cell is not changed. This means there is a limit on how many companies can be marked, however—the ColorIndex can only range between 0 and 56. The values actually assigned by the macro range from 3 to 56, so it is only possible to format 54 groupings of companies.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12673) applies to Microsoft Excel 2007, 2010, and 2013.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Conditional formatting is a powerful tool you can use to dynamically adjust the formatting of your worksheet. This tip ...Discover More
Sometimes the hardest part of getting your conditional formatting rules to work properly is figuring out the proper way to ...Discover More
You can use conditional formatting to add shading to various cells in your worksheet. This tip shows how you can shade cells ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.