Conditional formatting on Pivot Table

We have an Excel 2010 spread sheet with around 45,000 rows which is formatted into a Pivot Table which currently has 8,500 rows grouped into groups of 9 rows.  Two of the rows in the table are titled 'Balance' and 'Difference', as below:

Balance            3,000       3,000       3,000
Safety Stock      0       0       0
Difference      3,000       3,000       3,000

I am trying to find a way to format the background of all lines with 'Balance' with one colour and the lines with 'Difference' in another colour whilst leaving all other lines as white background.  I have tried using conditional formatting with a formula =ISNUMBER(SEARCH("Balance",$B:$B) but to no avail.

Does anybody have any suggestions?
Steve BrownTechnical DirectorAsked:
Who is Participating?
 
Swatantra BhargavaTechnology no BarCommented:
Hey

Please visit below may be its useful for you

http://community.qlik.com/thread/76045

SwAn
0
 
ProfessorJimJamCommented:
Select the whole used range and then go to conditional formatting then put this formula and select a color
=($B2="Balance")

then same follow for =($B2="Difference") and select another color.
0
 
Steve BrownTechnical DirectorAuthor Commented:
It sort of works, but not if I use the cell reference of $B2.  Strangely if I use $B7 for "Balance" and $B1 for "Difference" it seems to work, but then neither formatting survives a data refresh despite the 'Preserve cell formatting on update' being ticked on the PivotTable Options, Layout & Format Tab.
0
 
ProfessorJimJamCommented:
look,  first of all i do not know where your start range of your data is.   when you select the whole data, what is the start.  is it B1?   A1?  what is it?

can you attach a dummy example file. so that i set up the working formula in it?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.