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.
Also to add - I have the current date saved in a named range TodaysDate if that's of any help. I would assume it would help in question #2 but I'm not exactly sure how