Need a formula that will allow me to highlight a spreadsheet column.

Hi Clay,

I would do this with a conditional formatting rule based on a formula, rather than a formula in a cell.

Say your pay dates sit in row 2, in columns B to Z, and the data you want to highlight runs from row 2 down to row 100. Adjust the row and column letters to match your sheet.

1. Select the range B2:Z100.
2. Go to Home, then Conditional Formatting, then New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter this formula:

=B$2=MAXIFS($B$2:$Z$2,$B$2:$Z$2,"<="&TODAY())

5. Click Format, pick the fill colour you want, then click OK twice.

How it works: MAXIFS finds the latest pay date that is on or before today, and the rule highlights every cell in the column whose date in row 2 matches it. The $ before the 2 keeps the test on the pay date row, while the column letter stays relative so each column is tested against its own date. This also handles months with 2 to 4 pay dates, since it always picks the latest one that has already happened.

Two things to check if it does not work: make sure the pay dates are real dates and not text, and check the "Applies to" range in the Conditional Formatting Rules Manager matches the range you selected.

If your pay dates are in a different row, change the 2 in B$2 and $B$2:$Z$2 to that row number.

Thanks!