adding shapes based on cell values of dates

[chu123456]

hello, I am a beginner at VBA so simple explanation would be very helpful!

I created a gantt chart and want to add milestones which Ive done using .addshape. So if milestone = the cell date in the gantt chart then it will draw a shape there.

The problem is there are multiple milestones for each task (example below) and it ends up drawing all milestones into all tasks in the gantt chart. When each task has its own set of milestones.

Task Milestone
task a 1/1/2015 1/6/2015 2/5/2015
task b 2/5/2014 2/5/2016 4/6/2016

Below is my code, any suggestions? Any help would be greatly appreciated!!

Sub AddMilestone()


Set s1 = ThisWorkbook.Worksheets("Release Input")
Set s2 = ThisWorkbook.Worksheets("Release Calendar")
Dim rdate1 As Range
Dim rDate As Range
Set rdate1 = s1.Range("F2:I11")
Set rDate = s2.Range("e5:rz29")
Dim rCell1 As Range
Dim rCell As Range
Dim shp As Shape


For Each rCell In rDate.Cells
For i = 2 To 6 'rows
For j = 6 To 10 'columns


If s1.Cells(i, j).Value = rCell.Value And rCell.Interior.color

With rCell
Set shp = s2.Shapes.AddShape(msoShape5pointStar, .Left, .Top, .Width, .Height)
shp.Height = 15
shp.Width = 15
With shp.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 0)
.Transparency = 0
End With

Exit For

End With


i = i + 1
j = j + 1

End If
Next j
Next i
Next rCell
End Sub

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

I would like to help but am struggling to understand exactly how you are expecting this to work.

First, a few observations:

1. The IF statement is not complete in the code you posted.
2. You are changing the values of i and j inside For/Next loops that are also changing i and j. This is not a good idea.
3. You seem to be iterating over two areas on two sheets. I suspect the way to go will be to process one milestone line at a time. So:

a) Read in the milestone name;
b) Find that row on the second sheet;
c) Get the next milestone date;
d) Position the milestone shape on the second sheet
e) Repeat until this task is complete;
f) Move on to next task and repeat the above as necessary.

I presume that on the output sheet you have one row per task but I am not sure how the dates are arranged. Do you have one column per day? Are the columns labeled with the date? If so, You need to use both task and date to locate the grid for the milestone shape.