Offsetting array by another array and color selection line by line

[HansDK]

Hi all.

Disclaimer: I am still relatively new to vba

I am trying to create a calendar overview, a Gantt diagram of sorts (see attached picture). I have 1 column of dates in the format of dd-mm and next to it a column of duration of each activity in days (integer value).
The calendar overview is divided in to different sections with different color coding. For each section I wish to have a vba code selecting a horisontal selection of cells, defined as the starting date + duration. Then, I would like to fill color this line with a certain color (unknown at this point).

What I have done so far is this:

sub CalOver ()
       Dim Section1 As Range
       Dim duration1 As Range
        
    Set Section1 = Worksheets("Tender Schedule").Range("E10:E21")
    Set duration1 = Worksheets("Tender Schedule").Range("F10:F21")
    Range(Section1.Offset(0, duration1)).Select.Interior.Color = 49397
end sub

Whenever I run this, I get a "type mismatch" error.

If you can provide me with some help I would be grateful.

 

[JGordon11]

This will get you started and get past the type mismatch error. I doubt that it will put the color in the cells you want but I didn't have enough information about your spreadsheet structure to help you with that. Unless the coloring always starts a particular number of columns away from [Duration] it will be a matter of changing the Offset(0,1) to Offset(0,cols), and you'll need some additional code to determine each cols value as the code steps through Duration column.

Sub CalOver()
    Dim duration1 As Range, cl As Range
    Set duration1 = Worksheets("Tender Schedule").Range("F10:F21")
    For Each cl In duration1 'step through each cell in range duration
        With cl
            If IsNumeric(.Value) Then 'skip coloring if cl.Value is not numeric
                'if there's a number >= 1 in cl then color x cells horizontally starting one cell right of duration column (.offset(0,1))
                'where x is the value contained in cl converted to an integer, if x = 0 or negative do nothing to avoid causing an error
                If Int(.Value) > 0 Then .Offset(0, 1).Resize(1, Int(.Value)).Interior.Color = 49397
            End If
        End With
    Next cl
End Sub

 

[HansDK]

Hello again

Thank you very much for your reply. It helped a lot.

To answer your question, I can tell you that the starting position of each line of color should come from the column left of the duration column. This is also shown in the attached picture (perhaps poorly).