+ Reply to Thread
Results 1 to 8 of 8

How to choose a specific color in conditional formatting based on a formula

  1. #1
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    95

    How to choose a specific color in conditional formatting based on a formula

    Hi all,

    In the attached excel file - DM PR Availability sheet, Column C changes to Not Available based on the below criteria.

    1. If the value in column D is Do Not Disturb

    2. If the value in Column E is Out Of Office

    3. If the Review Status for an ID in Records sheet is Pending

    4. If the ID in Records sheet status contains 2 or more Review Status as Completed in a single day.


    Retaining all the above criteria, I would like to add a conditional formatting to the Column C in DM PR Availability sheet as below.

    If a person has 1 completed Review Status in a single day, then the Available status should be highlighted in Yellow (as shown in the file).

    Can someone help with the formula.

    Pass for sheets = 123

    Thanks in advance.
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: How to choose a specific color in conditional formatting based on a formula

    So... mg104360 should be highlighted in yellow... with what text? Available, or not available?

    This:

    If a person has 1 completed Review Status in a*single day, then the Available status should be highlighted in Yellow (as shown in the file).

    doesn't make much sense.

    1. What day? Any day? Some particular day?

    2. Where does the information exist, column S of DM records, or somewhere else??
    Glenn



  3. #3
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    95

    Re: How to choose a specific color in conditional formatting based on a formula

    Hi Glenn,

    Thank you for your response.

    The below formula has already been used in order to identify if an ID has 2 completed review status in column S of DM Records sheet , so that the column C in DM PR Availability sheet shows as Not Available.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    =IF(OR(D4="Do Not Disturb", E4="Out Of Office",COUNTIFS(Table5[ID2],'DM PR Availability'!A4,Table5[Review Status],"Pending")>0,COUNTIFS(Table5[ID2],'DM PR Availability'!A4,Table5[Test Column],TODAY(),Table5[Review Status],"Completed")>1),"Not Available","Available")
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Ans 1. Similarly, what i want is - in column S of DM Records sheet ,if an ID has 1 completed under "Review Status" in a single day, that is today then column C in DM PR Availability sheet should show as Available in Yellow color. However, the next day since the date has changed, it should be as its currently displaying i.e. Available highlighted in Green.

    Ans 2. Yes, the information exist in column S of DM records.

    Do let me know if anything further. Thanks.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,095

    Re: How to choose a specific color in conditional formatting based on a formula

    Eh??

    But today is 21 Feb. Don't you need that somewhere on your dashboard. Nobody has completed anything today. I am (terminally) confused.

  5. #5
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    95

    Re: How to choose a specific color in conditional formatting based on a formula

    So on 21 Feb - in column S of DM Records sheet ,if an ID has 1 Completed in today's date, then column C in DM PR Availability sheet should show as Available in Yellow color. However, the next day on 22 Feb, it should be as its currently displaying i.e. Available highlighted in Green.

    There's no specific date. Instead it should be today's date.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,063

    Re: How to choose a specific color in conditional formatting based on a formula

    I feel as if this does what you want:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  7. #7
    Registered User
    Join Date
    01-01-2022
    Location
    India
    MS-Off Ver
    2019
    Posts
    95

    Re: How to choose a specific color in conditional formatting based on a formula

    Perfect JeteMc. This is exactly what i wanted.

    Thanks a bunch. Really appreciate your help and efforts.

    Also thank you Glenn for your efforts and time.

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    14,063

    Re: How to choose a specific color in conditional formatting based on a formula

    You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

+ 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. [SOLVED] conditional formatting with specific range and color code
    By mckeamic in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-13-2021, 11:21 AM
  2. Replies: 15
    Last Post: 12-23-2019, 01:53 PM
  3. Conditional Formatting no longer letting me choose font color
    By houde in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-01-2019, 07:40 PM
  4. [SOLVED] Conditional Formatting specific text color
    By RJ1969 in forum Excel Programming / VBA / Macros
    Replies: 31
    Last Post: 07-26-2018, 11:16 AM
  5. Replies: 6
    Last Post: 05-17-2007, 07:08 AM
  6. [SOLVED] Conditional Formatting Formula to Color Rows Based on Column Value
    By SteveC in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-26-2006, 01:25 PM
  7. [SOLVED] Using either 'Conditional Formatting or VB' to color specific Cells
    By jsaintjohn1@yahoo.com in forum Excel General
    Replies: 3
    Last Post: 04-27-2005, 06:57 AM

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