Conditional formatting for relative cell reference

Copper Contributor

Sorry if this has been addressed, I've searched and read through several prior discussions, but have not been able to figure this out.

I am trying to apply conditional formatting across a whole table of data that compares amounts to a cell relative to its position.  The table below describes what I am trying to do. I have tried many different things without success.  My hope is to be able to establish the conditional formatting in the yellow highlighted cells (A2-A4) and copy it across many columns and down it many other rows laid out identically.  Thanks for any guidance.



2 Replies


Select A2:A4. A2 should be the active cell in the selection.

Set the fill color to red. This will be the default.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Format only cells that contain'.

Leave the first drop-down set to 'Cell Value'.

Select 'less than' from the second drop-down.

In the box next to it, enter the formula

=OFFSET(A2, -MOD(ROW(A2)-1, 4), 0)

Click Format...

Activate the Fill tab.

Select yellow.

Click OK, then click OK again.

@HansVogelaarTHANK YOU!  That is brilliant!  Thank you for the detailed instructions and formula.