Gantt Chart - Short Time Interval (0.1s) and Colorful Bars needed

**starryjazz** · 05-06-2014, 05:15 AM

Hi all,

I am a VBA newbie, and need to make a Gantt chart for tact time table.
There are few problems below I can not figure out, so please help me if you have time, REALLY appreciate your kind reply!!

(1) I can not solve the Macro "unstop running" by the Object "Range.End.Column", (Range.End.Row works fine to me)
so I REM them and using actual cell numbers for last Column.(it's not elegant and ugly, I know)

(2) Try to use Interior.Color to make the color of every generating cell can be the same color of corresponding cell in column "Type", but it just won't work...

(3) The speed of Macro processing is slow to me. Maybe it just because too many cells in a row?!(over 500 cells in a row, and I need 1500+ later...)

**Bernie Deitrick** · 05-06-2014, 07:02 AM

Delete the 0 and 1 values from your columns, and use these macros - only looping on rows.

Option Explicit

Sub gantt_chartHalfSec()

Dim i As Integer

For i = 3 To 19
Cells(i, 6).Offset(0, Cells(i, 5) / 0.5).Resize(1, (Cells(i, 6) - Cells(i, 5)) / 0.5).Interior.Color = Cells(i, 3).Interior.Color
Next i

End Sub
Sub gantt_chartTenthSec()

Dim i As Integer

For i = 3 To 19
Cells(i, 6).Offset(0, Cells(i, 5) / 0.1).Resize(1, (Cells(i, 6) - Cells(i, 5)) / 0.1).Interior.Color = Cells(i, 3).Interior.Color
Next i

End Sub

**starryjazz** · 05-06-2014, 09:36 PM

Hi Bernie,

Deeply appreciate and thanks so much!!

Your Macro has lightning speed, awesome!!

And I just added "+0.5"(and "+0.1" for another Macro) in your code for making every bar could follow previous one more closely:
Cells(i, 6).Offset(0, Cells(i, 5) / 0.5).Resize(1, (Cells(i, 6) - Cells(i, 5)+0.5) / 0.5).Interior.Color = Cells(i, 3).Interior.Color

THANK YOU AGAIN ^_^y