1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional formatting - 200 text values question

Discussion in 'Ask an Excel Question' started by Leigh Digons, Apr 20, 2017.

  1. Leigh Digons

    Leigh Digons Member

    Messages:
    49
    I am using this formula in my conditional formatting =MATCH(B28,$X$28:$X$28,0) and it works fine for one value.

    X28 is where I have one value listed. I want to do the same for 200 of these values (I can list them in a row somewhere or column if need be) and highlight the cells in a different area where these values show up.

    How do I make one rule (or just a couple) that highlights 200 different colors or formats.
  2. Leigh Digons

    Leigh Digons Member

    Messages:
    49
    This works using VBA but only up to 80 cells:

    Sub ColorCompanyDuplicates()
    'Updateby Extendoffice 20160704
    Dim xRg As RangeDim xTxt As StringDim xCell As RangeDim xChar As StringDim xCellPre As RangeDim xCIndex As LongDim xCol As CollectionDim I As LongOn Error Resume NextIf ActiveWindow.RangeSelection.Count > 1 ThenxTxt = ActiveWindow.RangeSelection.AddressLocalElsexTxt = ActiveSheet.UsedRange.AddressLocalEnd IfSet xRg = Application.InputBox("please select the data range:", "Kutools for Excel", xTxt, , , , , 8)
    If xRg Is Nothing Then Exit Sub
    xCIndex = 2
    Set xCol = New Collection
    For Each xCell In xRg
    On Error Resume Next
    xCol.Add xCell, xCell.Text
    If Err.Number = 457 Then
    xCIndex = xCIndex + 1
    Set xCellPre = xCol(xCell.Text)
    If xCellPre.Interior.ColorIndex = xlNone Then xCellPre.Interior.ColorIndex = xCIndex
    xCell.Interior.ColorIndex = xCellPre.Interior.ColorIndex
    ElseIf Err.Number = 9 Then
    MsgBox "Too many duplicate companies!", vbCritical, "Kutools for Excel"
    Exit Sub
    End If
    On Error GoTo 0
    Next
    End Sub
  3. Leigh Digons

    Leigh Digons Member

    Messages:
    49
    Also a clue: it's not the number of cells, it's the number of duplicate companies that cause the formula to stop highlighting. So if I change the company to one of the companies already highlighted as a duplicate, it will highlight in cell 81 or further. So I think the formula needs to be adjusted to allow for more duplicate companies.
  4. Leigh Digons

    Leigh Digons Member

    Messages:
    49
    Then if I change the company name, it still highlights the same color as the OLD name even if I rerun the VBA
  5. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Leigh Digons
    Do You really want to use 'Conditional Formatting' for this kind of case?
    Could You send a sample file?
  6. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,456
    Having that many different CF will eat up system resource and will hinder performance. As vletm wrote, upload a sample file for better assistance.

Share This Page