Macro to calculate dates between two color range in gantt chart

[harry1805]

Hi,

I need help in calculating start and finish date in a gantt chart color range. I have a excel with some data and gantt chart with color range between dates. I wanted a macro which show me the start and finish between a gantt chart color range.

Attaching screenshot.

Thanks

 

[Peter_SSs]

I assume that the coloured cells have been coloured manually?
You could try this in a copy of your workbook.

Sub TaskDuration()
  Dim rMonthHeaders As Range, c As Range, rStart As Range, rEnd As Range
 
  Set rMonthHeaders = Range("F1:Q1")  '<- edit to suit your data
  Application.FindFormat.Clear
  Application.FindFormat.Interior.Color = RGB(0, 176, 240)  '<- Edit to match your cell colour RGB value
  For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
    With Intersect(c.EntireRow, rMonthHeaders.EntireColumn)
      Set rStart = .Find(What:="", After:=.Cells(.Cells.Count), SearchDirection:=xlNext, SearchFormat:=True)
      If Not rStart Is Nothing Then
        c.Offset(, 1).Value = Intersect(rStart.EntireColumn, rMonthHeaders).Value
        Set rEnd = .Find(What:="", After:=rStart, SearchDirection:=xlPrevious, SearchFormat:=True)
        c.Offset(, 2).Value = Intersect(rEnd.EntireColumn, rMonthHeaders).Value
        c.Offset(, 3).Value = rEnd.Column - rStart.Column + 1
      End If
    End With
  Next c
  Application.FindFormat.Clear
End Sub

Here is my test sheet after the code has been run. It produced the values in B2:D7
If this is not what you wanted, please clarify.

harry1805 2020-02-09.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Task	Start	End	Duration		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	task1	Mar	Jun	4
3	task2	Apr	Jul	4
4	task3	Aug	Nov	4
5	task4
6	task5	Apr	Jun	3
7	task6	Jan	Jan	1
Sheet1