Highlighting Duplicate and Unique Values in Excel 2010

  • Article

Office Quick Note banner

Effectively Using Colors in Microsoft Excel 2010: Learn how to highlight duplicate and unique values in a range of numbers in Microsoft Excel 2010.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

Highlighting numbers by using colors is a great way to call attention to certain values. In this topic, you programmatically create a range of random numbers and then highlight the duplicate and unique values by using color. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates a range of values and then colors duplicate and unique values in that range.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub DemoAddUnique()
  ' Set up a range, and fill it with random numbers.
  Dim rng As Range
  Set rng = Range("A1:E10")
  SetupRangeData rng

  ' Clear any existing format conditions.
  rng.FormatConditions.Delete

  ' Display all unique values in red.
  Dim uv As UniqueValues
  Set uv = rng.FormatConditions.AddUniqueValues
  uv.DupeUnique = xlUnique
  uv.Interior.Color = vbRed

  ' Display all duplicate values in yellow and in bold.
  Set uv = rng.FormatConditions.AddUniqueValues
  uv.DupeUnique = xlDuplicate
  uv.Interior.Color = vbYellow
  uv.Font.Bold = True
End Sub

Sub SetupRangeData(rng As Range)
  rng.Formula = "=RANDBETWEEN(1, 100)"
End Sub

  5. Close the Visual Basic Editor.

Test the Solution

In this task, you run the VBA code that creates a random range of values and then highlights the duplicate and unique values in that range. Duplicates values are yellow; unique values are red.

To run the code

  • On the Developers tab, click Macros, select DemoAddUnique, and then click Run. Examine the results as shown in Figure 1.

    Figure 1. Running the code highlights duplicate and unique values

    Code highlights duplicate and unique values

Next Steps