Conditional formatting with date comparison

New Contributor

What I'm trying to do:

Get the spreadsheet to format the column left & right borders red for the column date that corresponds with any date in column E. For example, if E9 displays 4/18/22 then the 4/18/22 column left & right borders will be red, if E10 displays 5/6/22 then the 5/6/22 column left & right borders will be red, and so on.

 

adsummers_0-1650565699651.png

adsummers_1-1650565760063.png

What I have done:

I got column K to work using conditional formatting formula =IF((E$9)>0,AND(K$5=E$9)) applied to =$K$5:$K$44, but I cannot expand that result to the rest of the spreadsheet. I cannot figure out how to write the formula correctly to do this and every time I make a change the successful result in column K breaks. 

6 Replies

@adsummers 

Select the entire range that you want to format conditionally. I will assume that K9 is the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=COUNTIF($E$9:$E$44,K$5)

If the dates in column E extend further down than row 44, adjust the formula correspondingly.

Click Format...

Activate the Borders tab.

Specify red left and right borders.

Click OK, then click OK again.

@Hans Vogelaar Thank you for your reply. Unfortunately, it did not work for what I'm trying to accomplish, but it also didn't "break" what was already working. 

 

If there is a date in any cell in column E, I want it to format the corresponding date column. The date columns are K through RR (currently). I tried using your formula but adjusting to =COUNTIF($E$9:$E$44,K$5:$RR$5) but that didn't work either. (Disregard the green highlight, that is a separate formula showing "today".)

 

adsummers_0-1650637158937.png

 

@adsummers 

Could you attach a sample workbook without sensitive data, or if that is not possible, could you make it available through one of the websites that let you upload and share a file, such as OneDrive, Google Drive, FileDropper or DropBox. Then post a link to the uploaded and shared file here.

@adsummers 

Thanks. The rule should use formula

=COUNTIF($E$9:$E$50,K$5)

Exactly like I proposed, except that it uses 50 instead of 49.

And it should apply to $K$5:$RR$50, not just to $K$5:$K$50.

See the attached version, in which I entered a few dates in column D.

Thank you! I will try this shortly but looks like it is working.