Conditional formatting - 200 text values question

[Leigh Digons]

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.

 

[Leigh Digons]

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

 

[Leigh Digons]

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.

 

[Leigh Digons]

Then if I change the company name, it still highlights the same color as the OLD name even if I rerun the VBA

 

[vletm]

Leigh Digons
Do You really want to use 'Conditional Formatting' for this kind of case?
Could You send a sample file?

 

[Chihiro]

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