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


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




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



1) Try


2) No idea.