+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

  1. #1
    Registered User
    Join Date
    11-22-2018
    Location
    Bucharest
    MS-Off Ver
    Microsoft® Excel® 2019 MSO (Version 2110 Build 16.0.14527.20234) 32-bit
    Posts
    20

    Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

    Hello and nice to meet you all !

    A good friend of mine recommended this website and suggested that here all the brightest and modest minds gather to help solve together all kinds of problems that we encounter in our daily excel based activities.

    My Role : Junior PM
    Excel Level : Basic
    Excel Usage: Gant Charts
    Excel Formula used : =IF(AND(K$3>=$E4,K$3<=$F4),"x","") - helps display with "x" the days that a task is being worked on.
    Issue : I want to use conditional formatting to highlight (Green, Yellow, Gray, Red ) tasks that have the following status : Done / WIP / Not Started / Blocked.

    Edit about usage: The scope would be to paste the formatting on all blue cells in order to automate the colors and "x" spots each time the tasks enter different status.
    Task count: 50 - 100

    Attached you will find the test sheet that I am working for, if you could also help me find a way to drag/use the formatting on multiple columns and rows that will be much appreciated.

    Excel Issue.JPG

    Best regards,
    Razvan
    Attached Files Attached Files
    Last edited by Stancur; 11-22-2018 at 04:35 PM. Reason: Adding Attachment

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

    Without a sample spreadsheet to test from (can't test on a picture), untested, try this...

    Conditional Formatting
    • Highlight applicable range >> E4:AS7
    • Home Tab >> Styles >> Conditional Formatting >> New Rule
    • Select a Rule Type: Use a formula to determine which cells to format
    • Edit the Rule Description: Format values where this formula is true: =AND($D4="Done",E$3>=$B4,E$3<=$C4)
    • Format… [Number, Font, Border, Fill] >> you need to make one of these conditions for every color choice
    • OK >> OK
    Last edited by jeffreybrown; 11-22-2018 at 04:37 PM.
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    11-22-2018
    Location
    Bucharest
    MS-Off Ver
    Microsoft® Excel® 2019 MSO (Version 2110 Build 16.0.14527.20234) 32-bit
    Posts
    20

    Re: Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

    Hey Jeffrey,

    I've added a testable sheet also, but I will try what you recommended.

    Best regards,
    Razvan

  4. #4
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,178

    Re: Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

    Here's the file I worked on...
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-22-2018
    Location
    Bucharest
    MS-Off Ver
    Microsoft® Excel® 2019 MSO (Version 2110 Build 16.0.14527.20234) 32-bit
    Posts
    20

    Re: Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

    Thank you Jeff ! works like a charm.

    Best regards,
    Razvan

+ 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: 7
    Last Post: 03-07-2018, 03:10 PM
  2. Calculate next due date based on start date frequency and current date
    By ironoverload in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-09-2014, 07:08 AM
  3. Replies: 3
    Last Post: 11-23-2013, 06:21 AM
  4. Conditional Formatting based on end date and status
    By bunchomunkies in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-02-2013, 02:10 PM
  5. Replies: 0
    Last Post: 03-30-2012, 01:56 PM
  6. Conditional Formatting to Indicate Start Date & End Date
    By NiTRO-[X] in forum Excel General
    Replies: 4
    Last Post: 11-07-2011, 11:27 AM
  7. Start and END date -- Status
    By Dyzone in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-24-2010, 10:19 AM

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