Conditional Formatting - Two Columns for Same Date that lives in a merged cell

I have figured out how to select an entire range of cells and create a new rule for conditional formatting based on the date in the top cell. However I have not been able to figure out how to include the column next to it that also lives directly beneath the date, the date is in a merged cell. See screenshot

excel-help.jpg

You can see in the screenshot, the column for Sun 7/2/17 and # is formatted, but not the TIME column that is also associated with that same date. Tomorrow, the next two columns should automatically format accordingly as well, and so on.

I've tried selecting just the TIME columns and creating a new rule directed toward them, but that isn't working for some reason. What else do I need to do?

Thank you for your help.

But the solution is to come up with a condition that satisfies the leftmost cell in merged range, and make that reference absolute
So in below example, condition is satisfied if cell D2 contains today's date

The issue I'm finding with that, is how does the condition understand it's to apply to tomorrow on 6/8 where today would now be in AC3, and the day after that on 6/9 where today would be in AE3, if I use an absolute reference for $AA$3?

I've found using =$N$3=TODAY() formats the entire cell range of =$N$3:$BV$35 to the desired format intended only for the today() columns.

ok, thank you for the clarification. It stinks that's the way it needs to be done because it adds to the visual busyness of the worksheet, however I understand that I must surrender. Too bad there isn't a way to 'hide' a date in the E3 cell that is part of the merge and then it could both look pleasant and work correctly. Or, when the cell is merged, then use a formula to select both as the same D3:E3=today() since they are merged, they both should be considered having the same value. Something for Excel to think about.

Thank you again for your help

Originally Posted by kev_

As mentioned in my post, merged cells cause too many problems.
because the cell it needs does not exist (only the leftmost cell in a merged range remains)

What do you think of something like this, will it work in my circumstance? (I know very little VBA, so I'm not sure if it's addressing my issue). I found it here: https://superuser.com/questions/3763...-for-first-row

Please Login or Register  to view this content.

Originally Posted by kev_

You could hide row 3 and format merged cell as before if you want sheet to look the same

Yes, this is a great idea and I have done it. Thanks.