Gantt Chart Problem

[robcarr]

Dear All,

An ex colleaque created some coding to format some times into a gantt chart format automtically using VBA, the coding used to work fine, and now I can not get it to format if a cell has data, all it does now is fill all the blanks wiuth random yellow everywhere. Can someone have a look and let me know what is going wrong. Here is the coding, first bit is new coding, second bit is old version.

New Coding

Sub Monday()

'############# monday ###################
Application.ScreenUpdating = False
lowerrange = 3
numbersrange = 505
total = 506
k = 0

For z = 68 To 90

Worksheets("Monday&quot.Range(Chr(z) & Format(total)).Value = ""
For i = lowerrange To numbersrange
Worksheets("Monday&quot.Range(Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 255)
Worksheets("Monday&quot.Range(Chr(z) & Format(i)).Borders.Color = RGB(0, 0, 0)
If Worksheets(&quot;Monday&quot.Range(Chr(z) & Format(lowerrange - 1)) >= Worksheets(&quot;Monday&quot.Range(&quot;B&quot; & Format(i)) And Worksheets(&quot;Monday&quot.Range(Chr(z) & Format(lowerrange - 1)) <= Worksheets(&quot;Monday&quot.Range(&quot;C&quot; & Format(i)) Then
Worksheets(&quot;Monday&quot.Range(Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 0)

k = k + 1
End If

Next
Worksheets(&quot;Monday&quot.Range(Chr(z) & Format(total)).Value = k
k = 0
Next
'########################################### set double letters
For z = 65 To 74

For i = lowerrange To numbersrange
Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 255)
Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Borders.Color = RGB(0, 0, 0)
If Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(lowerrange - 1)) >= Worksheets(&quot;Monday&quot.Range(&quot;B&quot; & Format(i)) And Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(lowerrange - 1)) <= Worksheets(&quot;Monday&quot.Range(&quot;C&quot; & Format(i)) Then
Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 0)
k = k + 1
End If
Next

Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(total)).Value = &quot;&quot;
Worksheets(&quot;Monday&quot.Range(&quot;A&quot; & Chr(z) & Format(total)).Value = k
k = 0
Next
Application.ScreenUpdating = True
End Sub

old coding

'########sunday############
lowerrange = 165
numbersrange = 186
total = 187
k = 0


For z = 68 To 90
Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(total)).Value = &quot;&quot;
For i = lowerrange To numbersrange
Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 255)
Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(i)).Borders.Color = RGB(0, 0, 0)
If Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(lowerrange - 1)) >= Worksheets(&quot;rota1&quot.Range(&quot;B&quot; & Format(i)) And Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(lowerrange - 1)) <= Worksheets(&quot;rota1&quot.Range(&quot;C&quot; & Format(i)) Then
Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 0)

k = k + 1
End If

Next
Worksheets(&quot;rota1&quot.Range(Chr(z) & Format(total)).Value = k
k = 0
Next
'########################################### set double letters

For z = 65 To 74
Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(total)).Value = &quot;&quot;
Next



For z = 65 To 74

For i = lowerrange To numbersrange
Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 255)
Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Borders.Color = RGB(0, 0, 0)
If Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(lowerrange - 1)) >= Worksheets(&quot;rota1&quot.Range(&quot;B&quot; & Format(i)) And Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(lowerrange - 1)) <= Worksheets(&quot;rota1&quot.Range(&quot;C&quot; & Format(i)) Then
Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(i)).Interior.Color = RGB(255, 255, 0)
k = k + 1
End If
Next

Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(total)).Value = &quot;&quot;
Worksheets(&quot;rota1&quot.Range(&quot;A&quot; & Chr(z) & Format(total)).Value = k
k = 0
Next
Application.ScreenUpdating = True
End Sub

sorry for the huge data, but couldnt think of anyway to do it.



Regards