Problem Copying Conditional Formatting Across a Range of Cells

[ndbennett]

I have a dashboard that specifies this month's income from various stores and also the previous month's income, each store having its own row. I want to include a traffic light in the cell for this month that turns green if this month's income is equal to or greater than the previous months * growth factor, else it is red.

In my example, this month's income from my first store is in cell B9, last month's in cell C9 and the growth factor for all stores (as a percentage) in C4.

On an individual line (taking row 9 as an example), the following Conditional Formatting works:

Green if B9>=$C$9*(1+$C$4)

But I have about 200 stores I want to compare so I tried to copy and paste the formatting, but the $ around C9 meant that all the subsequent stores were referencing C9 rather than their own prior month's data.

If I try to remove the $ from C9, I get the following message:

You cannot use relative references in Conditional Formatting criteria for color scales, data bars and icon sets.

I don't want to go through it line by line and manually change the Conditional Formatting for each store - is there an easy way to achieve what I am trying to do?

Thanks.​

 

[Norie]

How exactly are you applying the conditional formatting?

 

[ndbennett]

How exactly are you applying the conditional formatting?

This month's and the previous month's actual income values are picked up from a pivot table using an HLOOKUP expression.

In this month's cell I am using the standard means of entering Conditional Formatting: Home / Conditional Formatting / Icon Sets with the rule 'Format all cells based on their values'

That works OK for the top row so then I drag that row down the spreadsheet to catch all the subsequent pivot table values. All the values appear correctly with the correct values, just the conditional formatting is not following the cells as I drag them down.

It makes sense in a way - the $ round C9 locks on to that cell, I presume. It is just that I cannot find a way to apply the conditional formatting I want without the $.