Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Color unique value cells (Excel 2003)

    I am trying to write code to color the cells holding unique values. It does not work. Any suggestions?


    Function ColorUniqueValues(InputRange As Range) As Long

    Dim cl As Range, UniqueValues As New Collection

    Application.Volatile

    On Error Resume Next

    For Each cl In InputRange

    UniqueValues.Add cl.Value, CStr(cl.Value)

    Next cl

    On Error GoTo 0

    ColorUniqueValues = UniqueValues.Cells.Interior.Color = 6

    End Function

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Color unique value cells (Excel 2003)

    Hi Lawrence

    This code just does a count of duplicates in a range.

    To show up duplicates you can use Conditional Formatting. This example assumes you have data in B2: B11, adjust the formula fit your data:

    Highlight cells B2: B11 and add this code to the Conditional Formatting

    =COUNTIF($B$2:$B$11,B3)>1
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Color unique value cells (Excel 2003)

    Shouldn't that be

    =COUNTIF($B$2:$B$11,B2)>1

    (Usually, when you select / highlight B2:B11, B2 will be the active cell)

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color unique value cells (Excel 2003)

    A function can not directly modify a cell, including its format, it can only RETURN a value to the formula that called it. To do what you are asking, you will have to use conditional formatting as specified in Hans modification to Jezza's response.
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Color unique value cells (Excel 2003)

    Thanks everyone for your help
    Lawrence

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •