Conditional Formatting based on cell that is # cells prior

%3CLINGO-SUB%20id%3D%22lingo-sub-2185080%22%20slang%3D%22en-US%22%3EConditional%20Formatting%20based%20on%20cell%20that%20is%20%23%20cells%20prior%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2185080%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20looking%20to%20use%20conditional%20formatting%20in%20a%20very%20specific%20way.%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20image-alt%3D%22Conditional%20Formatting%201.JPG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F260854iB6DEBDDCD010098F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Conditional%20Formatting%201.JPG%22%20alt%3D%22Conditional%20Formatting%201.JPG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20example%2C%20I%20want%20a%20cell%20to%20highlight%20based%20on%20the%20value%20in%20x%20amount%20of%20cells%20before%20it.%20For%20example%2C%20for%20row%204%2C%20where%20B%20is%20Annual%2C%20I%20want%20cells%20O4%20to%20highlight%20if%20it's%20the%20same%20as%20cell%20C4%20(12%20cells%20prior).%20For%20row%205%2C%20I%20can%20cells%20F5%20to%20highlight%20if%20it's%20the%20same%20as%20C5%2C%20and%20I5%20if%20it's%20the%20same%20as%20F5%2C%20etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20thoughts%3F%20Please%20note%20I%20do%20not%20want%20the%20whole%20row%20to%20highlight%2C%20JUST%20the%20latest%20cell%20that%20is%20the%20same%20as%20the%20prior%20quarter%2Fyear.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2185080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.