creating rows of colored cells from adjacent numbers
Roger,
Sub RogerGantt2()
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))
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
Next myCell
End Sub
HTH,
Bernie
MS Excel MVP
"Roger on Excel" wrote in message
...
Bernie,
Thanks - this is excellent !!
By the way, how would one add a outline border to the colored cells so
they
look like enclosed rectangles?
Also how would one automatically have the code select the numbered cells
so
that a button could be pushed and the macro would select all number cells
in
that column?
Thanks so much for your help
Best regards,
Roger
"Bernie Deitrick" wrote:
Roger,
Select the cells with the numbers, and run this
Sub RogerGantt()
Dim i As Integer
Dim t As Integer
Dim myCell As Range
t = 0
For Each myCell In Selection
For i = 1 To myCell.Value
t = t + 1
myCell.Offset(0, t).Interior.ColorIndex = 3
Next i
Next myCell
End Sub
HTH,
Bernie
MS Excel MVP
"Roger on Excel" wrote in
message
...
Hi ,
I have a column of numbers :
3
2
4
I want to make the corresponding number of adjacent cells appeared
colored.
For example next to the "3" would be three red cells, next to "2", two
red
cells, etc.
However I would like the colored cells to start after the previous
columns
colored cells so that a Gantt chart effect is provided.
Can anyone help?
Thanks,
Roger
|