Creating a Gantt based on values in cells

**vjboaz** · 07-09-2008, 02:20 PM

Based on a Beginning Date Value and an Ending Date Value - I would like to color a range of Cells - the month (dates) are in the header row.

For example: Beginning Date = 01/01/09
Ending Date = 06/01/09

I want the range of cells in the same row colored orange under Jan '09 to June '09.

Thanks!

**Richard Buttrey** · 07-09-2008, 05:51 PM

Thanks![/QUOTE]

Do you mean there are two header rows, one with the beginning date in say C1, and the end date in C2?

It might be better if you could upload a sample workbook so that we can understand the requirement in context.

Rgds

**Andy Pope** · 07-10-2008, 04:49 AM

Use conditional formatting.

**vjboaz** · 07-10-2008, 08:22 AM

I can't use conditional formatting because there is an additional determining factor as to color.

As you can see in the attachment - Column B - there is curriculum

So the cells are colored dependent on the curriculum - Math=Blue, Reading=Red, Social Studies=Pink and Science = Green

The Beginning Date header is Row 1, Column G.
The Endng Date Header is Row 1, Column H

Thanks!

Originally Posted by Richard Buttrey

Thanks!

Do you mean there are two header rows, one with the beginning date in say C1, and the end date in C2?

It might be better if you could upload a sample workbook so that we can understand the requirement in context.

Rgds[/QUOTE]

**Richard Buttrey** · 07-10-2008, 09:07 AM

I'm still confused. You seem to be suggesting there are two sets of conditions which define the colour you want. One depends on the 'Discipline' field, and the other is dependent on two dates.

Can you explain exactly how these relate to one another? Does one take precedence over another, or is one dependent on the other? Perhaps you could give a few examples of the colours you want in various cells and how you make that decision.

Rgds

**Andy Pope** · 07-10-2008, 09:32 AM

code to be placed in sheet object.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngDates As Range
    Dim strDiscipline As String
    Dim rngStartDate As Range
    Dim rngEndDate As Range
    Dim lngStartCol As Long
    Dim lngEndCol As Long
    Dim lngColourIndex As Long
    
    On Error GoTo ErrGantt
    
    If Not Intersect(Range("G:H"), Target) Is Nothing Then
        Set rngDates = Range("J1", Range("J1").End(xlToRight))
        If Len(Target.Value) = 0 Then
            ' clear
            Intersect(rngDates.EntireColumn, Target.Rows(1).EntireRow).Interior.ColorIndex = xlNone
        Else
            If Target.Column = 7 Then
                ' start date altered
                Set rngStartDate = Target
                Set rngEndDate = Target.Offset(, 1)
            Else
                ' end date altered
                Set rngEndDate = Target
                Set rngStartDate = Target.Offset(, -1)
            End If
                    
            With Application.WorksheetFunction
                lngStartCol = .Match(.HLookup(rngStartDate, rngDates, 1, True), rngDates, 0)
                lngEndCol = .Match(.HLookup(rngEndDate, rngDates, 1, True), rngDates, 0)
            End With
'Math=Blue, Reading=Red, Social Studies=Pink and Science = Green
            Select Case UCase(rngStartDate.Offset(, -5))
            Case "MATH"
                lngColourIndex = 41  ' Blue
            Case "READING"
                lngColourIndex = 3  ' Red
            Case "SOCIAL STUDIES"
                lngColourIndex = 7  ' Pink
            Case "SCIENCE"
                lngColourIndex = 10  ' Green
            Case Else
                lngColourIndex = 15  ' Gray
            End Select
            
            rngDates.Offset(Target.Row - 1, lngStartCol - 1).Resize(1, lngEndCol - lngStartCol + 1).Interior.ColorIndex = lngColourIndex
        End If
    End If

ErrGantt:
    Exit Sub
    
End Sub

vjboaz, it really help those trying to help you if you state what you have tried and why it did not work.
If you have a non standard layout that you mention such.

Or even better, as Richard mentioned, post an example.

**vjboaz** · 07-10-2008, 09:34 AM

I apologize for being so unclear in my explanation. In the attached sample, I think it indicates what the situation is.

Yes, the color is dependent on the discipline - the cell that gets the color is defined by the beginning and ending date.

I hope that helps. I just can't get the code to look at the range of dates in Row 1 (from J to AM) and then color the cell below it in the active row the appropriate color.

Thanks again for all your help.

Veronica

**Andy Pope** · 07-10-2008, 09:42 AM

So did you try my code?

You will need to adjust the colorindex values I used as they do not match yours exactly.

Also my code does not clear any existing formatting, unless the dates changing have no start or end.

If you do need existing colours on a row clearing first just copy the line

            ' clear
            Intersect(rngDates.EntireColumn, Target.Rows(1).EntireRow).Interior.ColorIndex = xlNone

Creating a Gantt based on values in cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Creating a Gantt based on values in cells

Thanks - Can't Use Conditional Formatting - 4 separate conditions

Gantt Chart

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions