+ Reply to Thread
Results 1 to 5 of 5

Help on conditional formatting for calendar

  1. #1
    Registered User
    Join Date
    03-30-2017
    Location
    Brazil
    MS-Off Ver
    2019
    Posts
    54

    Post Help on conditional formatting for calendar

    Hi everyone! I hope you are all well.

    I'm creating a calendar template based on some tutorials found on the internet.
    However, I'm having trouble creating a conditional formatting rule to apply to appointments on a certain day and at a certain time.

    The image below shows the model I was able to create. Already able to apply conditional formatting for weekends and holidays where the entire column is painted red and orange respectively.

    Capturar.PNG


    Now I need to create a conditional formatting rule to paint in green only the cells that represent an appointment for a certain time and a certain day. I want to paint just the cell and not the entire column

    1.PNG

    However, I still haven't been able to solve this case. can anybody help me?
    Attached is the template I created so far.

    Thank you very much in advance
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,720

    Re: Help on conditional formatting for calendar

    Your rule is using relative references. The date reference must use the absolute row and time reference must use absolute column. Also, you cannot add together the separate counts, you must use a single count that considers both date and time at the same time:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    I am not sure what is version 7 shown in your profile. If you have Excel earlier than 2007 let me know and I will provide an alternate formula.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-30-2017
    Location
    Brazil
    MS-Off Ver
    2019
    Posts
    54

    Re: Help on conditional formatting for calendar

    Thank you very much 6StringJazzer.
    This example works perfectly.

    Just in case I use this model in the 2007 version, what would the formula look like?

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,720

    Re: Help on conditional formatting for calendar

    The change applies to versions before 2007. There is no COUNTIFS before that. One solution is to add a helper column in column D of sheet Appointment, and use this formula to fill that column:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then your conditional formatting formula looks like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-30-2017
    Location
    Brazil
    MS-Off Ver
    2019
    Posts
    54

    Re: Help on conditional formatting for calendar

    Thanks a lot for the help!

+ 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. Calendar conditional formatting
    By heb1145 in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 08-19-2019, 09:30 AM
  2. Conditional formatting in a calendar
    By Geeza63 in forum Excel General
    Replies: 2
    Last Post: 06-19-2019, 01:44 AM
  3. Replies: 2
    Last Post: 10-03-2016, 08:35 AM
  4. Conditional Formatting Help on Calendar
    By Nevermore97 in forum Excel General
    Replies: 3
    Last Post: 06-18-2014, 01:50 AM
  5. Conditional Formatting - Calendar
    By efernandes67 in forum Excel General
    Replies: 4
    Last Post: 12-10-2010, 11:06 AM
  6. Conditional Formatting for Calendar
    By Peke in forum Excel General
    Replies: 2
    Last Post: 02-14-2010, 01:17 PM
  7. [SOLVED] Calendar - conditional formatting?
    By Valery2105 in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 06-27-2006, 01:45 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