Excel conditional formatting based on value of one cell to change fill on block of cells is not working properly. Unable to use format painter

Question

Hello all, I am working on Excel for Mac 365 (version 16.93.1). I have a block of cells highlighted that I would like the color of the fill to change if the conditions of one of the cells meets a certain criteria. If I use the $C$9 absolute reference, the block of cells highlights correctly. However, I have to make 80 of these blocks so I am trying to use relative references. If I choose C9 as the reference, only cell A1 fills correctly. If I try $C9, only the row 2 fills correctly. If I choose C$9 only column A fills correctly.

I want to use format painter but it won't work with absolute references. And now it seems it won't work with relative references either. I had done it this way for many years, then the last update from Microsoft screwed it up.

Is anyone else having this problem? Does anyone have a solution?

Answer 1

Hi Yvonne, are all your blocks on the same sheet and do you want the same rule applied across them?

In Excel for Mac 365, conditional formatting works based on the reference type: use $C$9 for all cells to point to the exact cell, or mixed references like $C9 or C$9 to copy across rows or columns. Format Painter does not reliably copy rules with absolute references, so I recommend selecting all the blocks first and then creating the rule, making sure the “Applies to” range covers all cells.