Conditional Formatting <0

[jgj1988]

I have pivot table in which I am trying to use conditional formatting based on 5 cells that I have setup up in column D. I want the rows inside the pivot table to be highlighted based on the value starting with cell R12 (part of the pivot table). I have been able to get 4 of the conditions to work correctly, its the last one less than or equal to 0% that is not working.

Here are my conditions:
Cell: D5(contains 46.2%) --Green = >=46.2%
Yellow= Between 40.2% and 46.2%
Cell: D9(contains (40.2%)--Light Red = <40.2%
Dark Red = <=0** Not working**
Orange = If text in column F (part of pivot begins with "MGS"

I tried playing with the order of the light red and dark red conditions but my cells that are less than 0 keep coming up with light red formatting instead of the dark red.

Here are the formulas I am typing in conditional formatting, in the same order as above:

$R12>=$D$5
IF($R12<$D$5,1," ")+IF($R12>$D$9,1," ")
$R12<=$D$9
$R$12<=0
LEFT($F12,4)="MGSV"


Does anyone see why my dark red condition ("=$R$12 <=0") is not working?

Another quick question I have is that every time I change a filter in my pivot table the cells that "Values" section of the pivot table are removed from the Conditional format Range, leaving only cells that are in the "Row labels" section. Is there a way to set my conditional format range to cells inside the pivot table while ignoring the header and footer section of the pivot?


Thank for your help,

James

 

[Hui]

CF's work in order and so if a value is <0 it is also <40.2

I'd change the line
Cell: D9(contains (40.2%)--Light Red = <40.2%

to
Cell: D9(contains (40.2%)--Light Red = >0 <40.2%

 

[jgj1988]

Thanks Hui,

I made that change and my cells less than 0 are no longer light red. They are now not formatted at all, my dark red conditional format is still not working. I tried changing to =IF($R$12<=0,1," ") but still not working. My values in column R are percents with 1 decimal place. For example: -23.2% could this be causing the less than or equal to 0 formula not to work?

Thanks again,

James

 

[NARAYANK991]

Hi James ,

Without your file to look at , I am not able to understand your problem , so I won't comment on that.

Regarding your apprehension that percents could be a problem , a value such as -23.2% is just a decimal number -0.232 ; the % sign is a part of the format , and when manually entered allows Excel to interpret it correctly. Thus entering 50% in a cell is the same as entering 0.5 in the cell and formatting it as Percentage. Thus entering any percentage value , between 0 and 100 , is the same as entering a decimal number between 0 and 1.

Narayan