Forum Discussion
Conditional Formatting based on cell that is # cells prior
I am looking to use conditional formatting in a very specific way. In this example, I want a cell to highlight based on the value in x amount of cells before it. For example, for row...
Select the cells that you want to format.
I will assume that C4 is the active cell in the selection.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula
=C4=OFFSET(C4,0,IF($B4="Yearly",-12,IF($B4="Quarterly",-3,-1)))
Click Format...
Activate the Fill tab.
Select a color.
Click OK, then click OK again.
Thanks so much! Two questions:
1) How would you modify this to ignore blank cells or ones containing "--"?
2) This file is intended to go on forever (in terms of year and month at the top of the sheet, based on the current date). Is there a way to make this only do the comparison for the last set of values, rather than the entire table?
1) Try
=AND(C4<>"",C4<>"--",C4=OFFSET(C4,0,IF($B4="Yearly",-12,IF($B4="Quarterly",-3,-1))))2) No idea.