Conditional formatting with Rows and Columns

I have a report with repeating rows based on Item and repeating columns based on date, the cells display the stock figure for the item/date combination. If the stock figure in a cell is <=0 the conditional formatting turns the cell red, otherwise it is green. This works fine, however I need to provide more detail.

In column J of every row I display the safety stock figure for the item. If the stock figure falls below the safety stock I want the cell to turn amber.

I created a conditional formatting rule to only format cells with a value between 1 and $J26. I believed this would allow the rule to repeat down with the rows, whilst ensuring the column remained fixed as the columns repeat along. However the rule for every cell remains the same and doesn't change.

Can anyone clarify if this is possible and if so, provide a solution?



Please sign in to leave a comment.