Color Scale Conditional Formatting With Relative References

Hi,

I'm having trouble with Color Scales within Conditional Formatting. I have a data set of commodity prices. In column A I have the name of the commodity, in column B I have the standard deviation of the price change of the commodity, and in Columns C-N I have the monthly % change in the commodity price. I want to conditionally format with Color Scales each row of price changes within Columns C-N based on each commodity's standard deviation (column B). If the price change is a one standard deviation or more decrease, I want the cell to be dark red; if the price change is less than a one standard deviation decrease, I want the cell to be a gradient of light red; if the price change is a one standard deviation or more increase, I want the cell to be dark blue; if the standard deviation is less than a one standard deviation decrease, I want the cell to be a gradient of light blue; and if the price change is 0, then I want the cell to be white.
I can achieve this perfectly by manually doing 3-Color Scale Conditional Formatting for each row, but it's very time-consuming. And Excel doesn't allow me to enter relative cell references when I'm doing the Color Scale Formatting. Is there a quick way that I can do this so that each row is color formatted differently?

I've attached an example file (there are many more rows within the original file) and formatted the first several rows manually as I want the final product to look.

Thank you very much in advance.

Thanks,
Mbp

I just did this to your sheet and it worked. I will send the sheet back with this post. Choose and higlight one of your conditionally formatted rows. Go to manage rules... under conditional fomatting. in the "applies to" box remove the "$" before the numbers 2 and 13. Hit ok. Then highlight the row again and use the format painter to apply to everything. Please let me know if this works. I am very curios!
Thanks.

Sorry forgot the attachment.

Hi,

Thanks for your response.
It didn't work unfortunately. Excel reverted back to absolute references even though I entered absolute references and the whole sheet was formatted based on just one of the commodity's standard deviation.
I'm just going to take care of it manually. Thanks though.