+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

    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.

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

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

    Merged cells cause too many issues - I try to avoid them
    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

    ConditFormat131.jpg
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

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

    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.

  4. #4
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

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

    As mentioned in my post, merged cells cause too many problems.
    Putting the date (that you want to use as basis of a condition) in a merged cell was unhelpful
    You have already realized that you cannot conditionally format your entire range with a single formula - the 2nd column in each pair will not work
    - that is because the cell it needs does not exist (only the leftmost cell in a merged range remains)

    Here is my suggestion to maintain the look of your worksheet:
    - see picture below
    - add another row and put the date in all columns
    - row 2 contains the date formatted as "ddddd"
    - formula in both D3 & E3 is = D2 and copied in (blocks of 2 cells) across row 3
    - row 3 date formatted as "dd-mmm"

    now can use a single formula that will work for all columns:
    =D$3=TODAY()

    ConditFormat132.jpg

  5. #5
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

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

    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

  6. #6
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

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

    Quote Originally Posted by kev_ View Post
    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.

  7. #7
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

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

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

  8. #8
    Forum Contributor
    Join Date
    10-25-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    292

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

    Quote Originally Posted by kev_ View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional Formatting - Comparing date columns
    By barnz in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-09-2015, 12:32 AM
  2. Conditional formatting in merged cells
    By davehow87 in forum Excel General
    Replies: 1
    Last Post: 08-11-2014, 04:56 AM
  3. Replies: 1
    Last Post: 03-12-2014, 08:45 AM
  4. Conditional Formatting of Merged Cells
    By ClubCrackerz in forum Excel General
    Replies: 0
    Last Post: 06-14-2012, 03:58 PM
  5. Conditional Formatting with Merged Rows
    By nszoke2418 in forum Excel General
    Replies: 2
    Last Post: 02-22-2012, 04:17 PM
  6. Conditional formatting columns based on Date
    By clowrym in forum Excel General
    Replies: 1
    Last Post: 09-23-2009, 04:55 PM
  7. Merged Cell Conditional Formatting Borders
    By T Kirtley in forum Excel General
    Replies: 2
    Last Post: 06-01-2006, 02:45 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1