Apr 21 2022 12:51 PM
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.
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.
Apr 21 2022 01:37 PM
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.
Apr 22 2022 07:20 AM
@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".)
Apr 22 2022 07:24 AM
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.
Apr 22 2022 07:32 AM
Apr 22 2022 07:48 AM
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.
Apr 22 2022 08:30 AM