VBA Color Cells For Gannt Chart

    I have been struggling for some time to find a simple code to color index cells that have two conditions, one being the task owner and the second being the time requirement.
    I have a drop down list for populating each row of a gannt chart with the owner, this owner also has an associated number referencing the allocated colour index number.
    To the right of the owner is a basic time line with each column represneting days, weeks or whatever measurement.
    These columns are populated simply with an "x".
    What I want to do is to choose the owner, go across to the time, type in an "x" in the time slots and the cells with an "x" are coloured with the relevant owners colr index.
    I have researched the threads, read my text books, looked up the help and found a lot of references which are around the solution but none which nail it.
    Looking forward to your assistance.
    Many thanks in advance,

    Re: Color Cells Based On Owner & Time (gannt Chart)g


    This should get you started. It will do the JF and FT's.


    Just copy the data for the FT's down for each successive color.


    Use your macro recorder to determine the color-number needed.


    Re: Color Cells Based On Owner & Time (gannt Chart)g


    Hi Kiwifinny,


    Code
    Sub AddColors()
    Dim c As Range
    Dim iCount As Integer
    iCount = WorksheetFunction.CountA(Range("A4:A1200")) + 3
    For i = 4 To iCount
        For Each c In Range("C" & i, Range("IV" & i).End(xlToLeft))
            If c.Value = "x" Then c.Interior.ColorIndex = Range("L" & i).Value
        Next c
    Next i
End Sub


    Should do it for you.


    Bill


    EDIT: I have placed the ColorIndex numbers for each color in the rows in Column L, you can have them wherever you like, just change the code where it says "L" & i.

    Re: Color Cells Based On Owner & Time (gannt Chart)g


    Hi Kiwifinny,


    If you could have blanks in column A, then you will have to adjust the code slightly. I have also changed the Range 'Names' by adding another column and adding the ColorIndex for each name into the new column, so that it automatically reads the color index when you select a new name in column A.


    I have added the code to the button on the sheet.


    Bill

    Re: Color Cells Based On Owner & Time (gannt Chart)g


    Hi Kiwifinny,


    You could also do this by using the Worksheet_Change() event.



    This will automatically colour the cells as you enter the 'x' or 'X'. It will also remove the cell colour if you delete the 'x'.


    Bill

    Re: VBA Color Cells For Gannt Chart


    Many thanks again guys, your help is terrific, I've learnt a lot both with the many ways you can approach a problem and I'll develop from the code supplied. Again huge thanks.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login