[Solved] Sorting a range of values correctly in vba

Use a helper column which can then be used for the numerical sort. So in the source data add a helper column next to the days column that goes 1,2,3,4 etc ranking days – you can use a vlookup to pull in the right rank for each days group. Then use this to sort on in your pivottable. Conditional formatting can then do your colouring as well based on the helper column value.

Data with helper column and vlookup table:

Note that this would be your actual backing data before aggregating up that would have the helper column.

Pivot using helper column to order (note: you would use Count not Sum):

Conditional formatting:

Add a conditional formatting rule (to meet your requirements) using the helper column value:

Result: