+ Reply to Thread
Results 1 to 8 of 8

conditional formatting and formulas for gantt charts

  1. #1
    Registered User
    Join Date
    06-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    12

    conditional formatting and formulas for gantt charts

    I've got a Gantt chart in Excel (see picture). I'm wanting to create a Cond Form rule that will highlight the cells black that correlate with the date in column E.
    For example; in the screenshot I would want cells U9, AC10, AV10, AC11-AE11 all black. This would essentially show that the associate is busiest on those days and cannot take on any other time commitments.

    What formula can I put into Cond Form to highlight cells referenced in column E black in the Gantt chart. Any help is appreciated.
    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
    25,424

    Re: conditional formatting and formulas for gantt charts

    A picture is not much use to us - it is better to attach a sample Excel workbook. The yellow banner at the top of the screen explains how you can do this.

    Pete

  3. #3
    Registered User
    Join Date
    06-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    12

    Re: conditional formatting and formulas for gantt charts

    I've attached the workbook. Thanks!!
    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
    25,424

    Re: conditional formatting and formulas for gantt charts

    You have shown those "busy" dates in a variety of ways - as a single date, as multiple individual dates separated by a comma, and as a date range separated by a hyphen, and presumably combinations of these formats. They do not lend themselves very easily to be picked up as individual dates. I suggest you list them against each person in a separate sheet, and that might make it a bit easier.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    06-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    12

    Re: conditional formatting and formulas for gantt charts

    Okay, I can do that. What formula would you use for conditional formatting to make cells black based on those dates.

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

    Re: conditional formatting and formulas for gantt charts

    Well, we have to get that other sheet sorted out first. Do you intend to put the person's name in cell B8, and then for the following rows to apply to him/her? Would you then put a second person's name in B14, and so on?

    Pete

  7. #7
    Registered User
    Join Date
    06-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    12

    Re: conditional formatting and formulas for gantt charts

    Yes- I'll include a screenshot with the update for busy dates. I'm doing a screenshot as the wkbk has sensitive information. Essentially, cell B7 will be an associate name, cells D8 through R8 will represent 'busy dates'. I then want these busy dates to be reflected on the calendar with a black box. I currently have it set to create bars of different colors based on start/end dates and project role. I'm wanting to put a conditional formatting rule over top of this that would make the bars for the specific busy dates black. In the screenshot I would want cells AJ8 and AO 8 to be black to align with the busy dates in cells D8 and E8.
    Attached Images Attached Images
    Last edited by sdegr001; 08-23-2021 at 01:56 PM.

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

    Re: conditional formatting and formulas for gantt charts

    That looks quite a bit different than the earlier file, although I can't make out any of the details.

    I worked on the earlier file while awaiting your response, and I went ahead with setting up a separate sheet of busy days. That now seems redundant if you are using cells on the same sheet to show those busy dates.

    Please anonymise your file to protect your sensitive information and then re-attach it, so I can work on the same version as you.

    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. Gantt Chart Conditional formatting
    By jsneak in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-30-2015, 07:19 AM
  2. [SOLVED] Gantt Charts & Conditional Formatting
    By highguyuk in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-10-2015, 04:27 PM
  3. Conditional formatting to measure actual vs planned Gantt chart
    By arnab0711 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-19-2015, 04:19 PM
  4. Create Conditional Formatting Gantt
    By mycon73 in forum Excel General
    Replies: 15
    Last Post: 10-03-2014, 08:49 PM
  5. Gantt Charts done with Conditional Formatting
    By nielsoneo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-16-2012, 02:25 PM
  6. Gantt Chart with conditional formatting
    By douglasdale in forum Excel General
    Replies: 2
    Last Post: 06-28-2010, 11:24 AM
  7. vacation GANTT Conditional formatting
    By DUKE888 in forum Excel General
    Replies: 7
    Last Post: 12-22-2009, 03:17 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