Keep Text color format from data to pivot table?

[lbrosten]

I have an excel worksheet where text data in some cells are changed to "red". I want that data to stay formatted "red" in any pivot tables created from that worksheet. Is this possible?

Example: Column "Hospital" has 25 entries. "General Hospital" font color is "Red". All other cells are black text. I create a pivot table and want "General Hospital" to always show in red text.

 

[jdellasala]

You can use Conditional formatting to make values in cell have whatever format you want. As such I was going to suggest using Array formulas rather than a Pivot Table, however Conditional Formatting does not recognize Array notation (#) (YET?), so if you still go the Conditional Formatting route, you need to make the area that the condition applies to as large as you anticipate needing for the data.
For example

Book1
	A
1	1
2	2
3	3
4	4
5	5
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A5	Expression	=ISODD(ROW())	text	NO

That works fine if the sequence is 5 or less, but if the sequence goes to 7 as it, it will fail. But if I don't expect it to grow past 40, then this would work:

Book1
	A
1	1
2	2
3	3
4	4
5	5
Sheet1

Cell Formulas
Range	Formula
A1:A5	A1	=SEQUENCE(5)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A40	Expression	=ISODD(ROW())	text	NO

Expanding the Conditional Formatting range to A40 means that all odd rows will have bold red text. You can use something more restricting as the formula so that a cell's format is changed only as needed. This was just an easy demo. It's all in the formula and range, even for a Pivot Table.
OK?

 

[lbrosten]

Thank you. In my scenerio, the hospital text field will be manually changed to red. There is no specific condition that could be applied.
Is this possilbe.to carry over the formating to the pivot...."If the data text in column A is red, then the make the text in the pivot table for this data point red.
Any chance that is possilble?

 

[jdellasala]

Uggg! Unfortunately the formula =CELL("color",A1) returns a 0 whether the Font or Fill has a color. I don't think it's supposed to, and I've asked MS about it.
Without that, (believe it or not) some kind of VBA would be needed, but not something I'm comfortable with.