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 4, where B is Annual, I want cells O4 to highlight if it's the same as cell C4 (12 cells prior). For row 5, I can cells F5 to highlight if it's the same as C5, and I5 if it's the same as F5, etc.
Any thoughts? Please note I do not want the whole row to highlight, JUST the latest cell that is the same as the prior quarter/year.
4 Replies
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
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.
