I'm trying to run a macro that helps me analyze monthly trend data. Ideally, the macro would highlight all cells where its respective absolute deviation is greater than the average deviation for its monthly trend(its row of data.) I already calculate the trend's average deviation for the monthly trend in a separate column and this formula has been copied down several rows. Any help is appreciated. I can post a sample spreadsheet if this wasn't clear. Thanks in advance!

Do you need a macro or would regular CF do?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

That's a good question. I have about 14 sheets I need to apply this formatting to and I need to sometimes be able to turn on/off the formatting for presenation purposes. Figured a macro allowing me to activate the formatting would be best but I'm open to suggestions. The other issue I had was with applying this formula relatively from cell to cell. The formula rule seem to stay the same through the range of data. I may not be using the CF menu correctly.

If you need to turn it off once in a while, a macro would definitely help. I'm sorry I'm not at all proficient with macros.

As for the usual CF you would use something like =a1:a10<$f$20 where f20 contains the comparison value. Note that the range is relative

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Trying the regular conditional format route but that isn't working. Even adjusting for the cell locks, the fomatting formula rule remains the same when I copy the format over into each cell in the range. Why does it do that? My rule is: =ABS(C4-$Q4)>$R4
every cell in the range reads this way and for example, in cell D9, it should read: =abs(D9-$Q9)>$R9 but it remains the same as the original. is this a limitation of CF or have set the function incorrectly? I figure I could always turn this into a macro by recording the steps but i can't even get this to work correctly on its own.

Perhaps post a small sample sheet ( no pics) so we can see what goes wrong

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

redacted version attached.

Attached Files

sample_trend.xlsx (10.8 KB, 7 views)

maybe it's just a limitation with relative references and conditiona formatting. any other ideas on how to achieve what i'm looking to do and get around this issue? the alternative is to create this rule cell by cell?

I don't see anything wrong.The references of the CF don't change when you select a cell because the CF applies to the entire range and not only to the cell you selected
Yoy can see for yourself that the formatting is correct by entering your CF formula in a cell and dragging right and down. There you will see the formula adapt,and the TRUE values correspond to coloured cells.
It is also so that any formula entered in CF is considered an array formula by XL, and that is how they behave.

In conclusion, your solution is perfectly valid

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post