Tod
I tested this code with 3 fields and 1000 records with 3 field
headers covering the range A1:C1001
The third field held the 6 values you mentioned in random order
The three fields were named FieldA, FieldB and FieldC
This procedure takes under a second to process 1000 records in FieldC
You will have to range name the field you want to process
after the ADO recordset is loaded using VBA. i.e Range C1:C1001 is
named FieldC
In my example the naming process is not shown
Note that V=Array statement has been split into 2 lines for ease of
reading but it should be all one line

'Put this in a standard module

Option Base 1 'put as first line of module

Sub DoFilter()
Dim v As Variant
Dim z As Variant
Dim x As Integer
Dim Rng As Range

Set Rng = Range("FieldC")
v = Array("ValueA", "ValueB", "ValueC", _
"ValueD", "ValueE", "ValueF")
z = Array(13, 5, 10, 6, 45, 3) 'list of colours
Rng.Cells(2).Select
With Selection
..AutoFilter 'turn on filter
For x = 1 To 6
..AutoFilter Field:=3, Criteria1:=v(x)
Rng.Select
DoFormat z(x)
Next
..AutoFilter 'turn off filter
End With
End Sub

Sub DoFormat(ByVal Colour As Integer)
Selection.SpecialCells(xlCellTypeVisible).Select
With Selection
..FormatConditions.Delete
..FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
..FormatConditions(1).Interior.ColorIndex = Colour
End With
End Sub