PivotTable cells losing colour when changing filter/refreshing.

Hoping someone can help with a (hopefuly) obvious answer.

We have a large table of data (about 500 rows and 30 columns).
I wanted to put this into a pivot table to display the information more easily.
Once I have selected the table, and created the pivot table, I tick most of the boxes to show the data.
This looks a mess.
So I make it tabular format and remove all subtotals/totals and it looks more like a table. Great stuff.
But, as soon as I add colour to the columns of the pivot table, it seems to bug out.

So column B is all orange, C blue and D purple.
Once I have coloured these columns, if I use the filter on the row to untick an entry that I don't want to see, random cells lose their colour. (The preserve formatting tick box was already checked).

Over a large table with 30 columns, this looked crazy and made no sense.
Cells lost colour, some were coloured all black, some text turned grey.

I thought I was an issue with the original table (which is coloured) so I copied the data to a new tab, pasted as values and had it just as text.
The pivot table still acts crazy when refreshing and I am baffled to why.
Because of this, I am unable to have the table how I want.

Any ideas on this?

The end game would be (if possible) for the pivot table to take the formatting of the source cell and bring it along. Is this possible?

I am trying to make a database that is accurate and looks good, but the visual side is annoying me.

Sounds just what I need.
You asked for the layout of the sheet, which information to you specifically need?
I can try to obtain all information in one go rather than a back and forth.

Originally Posted by xlnitwit

Basically just which columns you want coloured (or if it's all of them that's even easier) and which row it can pick up the required formatting from.

Ok. I will make up a temp spreadsheet to show the columns. How would this function? Would only the pivot table cells be coloured or the entire column? I ask because i planned on sticking information under the table?

Also, if one cell in a row contains data, the entire row needs to be red. This also doable?