You are correct that Airtable does not allow you to add colors to the cells in pivot tables.
Google Sheets does a great job with both both pivot tables and conditional color formatting, so I would recommend doing this with Make’s Google Sheets automations and Make’s Airtable automations.
You would typically setup the automation to dump all the necessary Airtable data onto its own tab in Google Sheets, and then on another tab in Google Sheets, you would have the pivot table that automatically generates itself from the dumped data. You would also set the conditional formatting there as well.
Below is a screenshot of a very simple way to set this up in Make, but this is just a very simple example of how you could do this in Make.
Most of my Make scenarios are significantly more complex than this, but this would be a good starting point for you.
This scenario creates the new spreadsheet, searches for specific records in Airtable, and then adds each record as a new row in the spreadsheet. (Make automatically loops through your found records, so you don’t need to specify any iterations.)
If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread:
For example, here is how you would instantly trigger a Make automation from Airtable.
— ScottWorld, Expert Airtable Consultant