Roger,

Just do a check that the value is numeric:

Option Explicit
Sub RogerGantt3()
Dim i As Integer
Dim t As Integer
Dim myCell As Range
Dim myA As Variant
Dim myLS As Variant
Dim myCol As Integer
Dim myRow As Long

myA = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)

myCol = 2 ' to do numbers in column B
myRow = 2 'Starting in Row 2

t = 1
For Each myCell In Range(Cells(myRow, myCol), _
Cells(Rows.Count, myCol).End(xlUp))
If IsNumeric(myCell.Value) Then
i = myCell.Value
If i < 0 Then
With myCell.Offset(0, t).Resize(1, i)
.Interior.ColorIndex = 3
For Each myLS In myA
With .Borders(myLS)
.LineStyle = xlContinuous
.Weight = xlThin ' or xlMedium
.ColorIndex = xlAutomatic
End With
Next myLS
End With
t = t + i
End If
End If
Next myCell

End Sub

HTH,
Bernie
MS Excel MVP


"Roger on Excel" wrote in message
...
Hi Bernie,

Thanks for getting back to me.

That does appear to be the problem line of code.

I have the following formula delivering results to the number column.

=IF(LEFT(B5,1)="b",Summary!B29,IF(LEFT(B5,1)="s",S ummary!B30,""))

I make a cell in the number column equal the cell with the formula above and
likewise for other numbers down the column.

The code works fine when a number is delivered by this formula, however it
grinds to a halt when it delivers the blank.

Ive tried

=IF(LEFT(B5,1)="b",Summary!B29,IF(LEFT(B5,1)="s",S ummary!B30,0))

But this doesnt work either.

I also tried to substitute the amendment you suggested, however it still
doesnt work.

An alternative will be for me to have a userform read the numbers from the
formula cells and then deliver actual integers to the number column, but this
would be a drastic solution.

What do you think?

Best regards,

Roger