Conditional Formatting based on cell that is # cells prior

New Contributor

I am looking to use conditional formatting in a very specific way. 

Conditional Formatting 1.JPG

 

 

 

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

@Linehan13101 

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.

@Hans Vogelaar 

 

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?

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

@Linehan13101 

 

1) Try

=AND(C4<>"",C4<>"--",C4=OFFSET(C4,0,IF($B4="Yearly",-12,IF($B4="Quarterly",-3,-1))))

2) No idea.