Apr 21 2022 12:51 PM
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
If the dates in column E extend further down than row 44, adjust the formula correspondingly.
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
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.