+ Reply to Thread
Results 1 to 6 of 6

Complex Conditional Formatting with data from other cells (Calendar)

  1. #1
    Registered User
    Join Date
    02-09-2021
    Location
    Milwaukee, WI
    MS-Off Ver
    365
    Posts
    6

    Complex Conditional Formatting with data from other cells (Calendar)

    Hello,
    I am creating a calendar to show the schedules of certain devices. What I am looking to do is have the date cell on the calendar change to yellow if the data cell is different than the data in the day before AND not highlight if the day before is empty AND not highlight if the day before contains "5:00". In the attached picture, February 8th and 10th are highlighted as appropriate. February 11th should NOT be highlighted because it is technically the same schedule as the 10th. The 05:00 on the 10th simply says what time it should change. Below is what I have thus far. The calendar goes from columns D to J. This particular formula below is performing conditional formatting on cell E11 (February 8th).

    =AND(E$12<>D$12, D$12<>"")

    Since I want to apply this to all dates, I have left the row absolute and the column variable. I had to do a special format for Sunday since it looks at the week above. I just need the last piece to my puzzle. "SEARCH" has not worked.
    I think the main issue is that I just want to search if the cell contains "5:00". D$12<>"5:00" does not work either.

    =AND(E$12<>D$12, D$12<>"", ??????????)

    I had to post a picture because the VBA and equations won't allow me to copy/paste a readable calendar that doesn't contain sensitive data.
    Attached Images Attached Images

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Conditional Formatting with data from other cells (Calendar)

    Welcome to the forum.

    It would help if you attached a sample Excel workbook, so we can see how that data is populated. See the yellow banner at the top of the screen for details of how to do this.

    Pete

  3. #3
    Registered User
    Join Date
    02-09-2021
    Location
    Milwaukee, WI
    MS-Off Ver
    365
    Posts
    6

    Re: Complex Conditional Formatting with data from other cells (Calendar)

    Attached is a hyper rudimentary version of what I'm working with.
    I know A1 doesn't have conditional formatting applied, it's not supposed to since it references "the week before" which doesn't exist in this sample sheet.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Conditional Formatting with data from other cells (Calendar)

    For cell E11 (as used in your first example), you can change the CF formula to this:

    =AND(E12<>D12, D12<>"",ISERROR(FIND("5:00",D12)))

    and by making the rows relative to row 11 (by removing the $ signs) this means that you can use the Format Painter icon to apply the same format to the date cells below.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    02-09-2021
    Location
    Milwaukee, WI
    MS-Off Ver
    365
    Posts
    6

    Re: Complex Conditional Formatting with data from other cells (Calendar)

    That's it! Thank you!

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Complex Conditional Formatting with data from other cells (Calendar)

    Glad to hear it, and thanks for marking the thread as Solved.

    As you are relatively new to the forum, you might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  2. Complex Conditional Formatting Help
    By bathtubs in forum Excel General
    Replies: 8
    Last Post: 12-30-2015, 03:15 PM
  3. highlight cells in a calendar with conditional formatting
    By eddie2424 in forum Excel General
    Replies: 5
    Last Post: 12-23-2009, 05:32 PM
  4. [SOLVED] How do I do a complex conditional in a conditional formatting formula
    By Ray Stevens in forum Excel General
    Replies: 6
    Last Post: 03-12-2006, 06:30 PM
  5. [SOLVED] complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 05:05 PM
  6. complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 04:05 PM
  7. [SOLVED] complex?? Q about Conditional formatting
    By AngelaG in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM

Tags for this Thread

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