Conditional formatting for relative cell reference

[Mark_Kimura_1966]

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]

@Mark_Kimura_1966 

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.

[#3]

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