Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
Berinie,
This is excellent - thanyou so much for your help Best regards, Roger |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
Bernie, ive utilized your code in my spreadsheet, however im having a "type
mismatch" problem with the formulas in the cells with the numbers. Can you help? Roger "Roger on Excel" wrote: Berinie, This is excellent - thanyou so much for your help Best regards, Roger |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
Roger,
What line does it fail on? Anyway, try changing i = myCell.Value to i = CInt(myCell.Value) Your values may be strings... Bernie "Roger on Excel" wrote in message ... Bernie, ive utilized your code in my spreadsheet, however im having a "type mismatch" problem with the formulas in the cells with the numbers. Can you help? Roger "Roger on Excel" wrote: Berinie, This is excellent - thanyou so much for your help Best regards, Roger |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
creating rows of colored cells from adjacent numbers
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add cells on two adjacent rows but non-adjacent columns | Excel Worksheet Functions | |||
How do I fill adjacent cells with incrementing numbers | Excel Discussion (Misc queries) | |||
How to add numbers in adjacent cells | Excel Discussion (Misc queries) | |||
Converting colored cells to numbers- is there a formula? | Excel Worksheet Functions | |||
Cell right next to colored cells is automatically colored on entering a value | Excel Programming |