| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
shading cells trouble
I'm not very good with scripting.
this script automatically shades cells based on values that i enter. The script runs every time i edit a value in a cell. It checks about 300 rows in about 10 columns. The problem is, it takes very long. I dont really need to check every cell between the F column and P column. how do i specify this code to only update the cell shading of Cell F25:F324 , H25:H324, J25:J324, L25:L324, N25:N324, and P25:P324 OR if its possible, instead of updating up to the last row of the those columns specified, how about the code only checks up to the row i am currently working on? Another question i have is... Before i start, i know what row i will be going up to. can i specify this in some cell or pop up window, and have my script's range automatically update to that row? (instead of up to row 324)? TIA Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("F25:F324", "P25:P323") Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 ' dark green Case 1 icolor = 45 'light orange Case 2 icolor = 4 ' bright green Case 3 icolor = 10 ' green Case 4 icolor = 5 ' blue Case 5 icolor = 48 ' gray Case 6 icolor = 9 ' dark red Case Is 6 icolor = 3 ' red Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P323")) Is Nothing Then ColorCells For Each c In Target Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 'shrug Case 1 icolor = 45 'light orange Case 2 icolor = 4 'bright green Case 3 icolor = 10 'green Case 4 icolor = 5 'blue Case 5 icolor = 48 'gray Case 6 icolor = 9 'dark red Case Is 6 icolor = 3 'red Case Else 'whatever End Select c.Interior.ColorIndex = icolor Next c End If End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
shading cells trouble
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P323")) Is Nothing Then if c.Value <"" then ' May speed up things a little ColorCells For Each c In Target Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 'shrug Case 1 icolor = 45 'light orange Case 2 icolor = 4 'bright green Case 3 icolor = 10 'green Case 4 icolor = 5 'blue Case 5 icolor = 48 'gray Case 6 icolor = 9 'dark red Case Is 6 icolor = 3 'red Case Else 'whatever End Select c.Interior.ColorIndex = icolor Next c End If end if End Sub "Mo2" wrote in message ... I'm not very good with scripting. this script automatically shades cells based on values that i enter. The script runs every time i edit a value in a cell. It checks about 300 rows in about 10 columns. The problem is, it takes very long. I dont really need to check every cell between the F column and P column. how do i specify this code to only update the cell shading of Cell F25:F324 , H25:H324, J25:J324, L25:L324, N25:N324, and P25:P324 OR if its possible, instead of updating up to the last row of the those columns specified, how about the code only checks up to the row i am currently working on? Another question i have is... Before i start, i know what row i will be going up to. can i specify this in some cell or pop up window, and have my script's range automatically update to that row? (instead of up to row 324)? TIA Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("F25:F324", "P25:P323") Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 ' dark green Case 1 icolor = 45 'light orange Case 2 icolor = 4 ' bright green Case 3 icolor = 10 ' green Case 4 icolor = 5 ' blue Case 5 icolor = 48 ' gray Case 6 icolor = 9 ' dark red Case Is 6 icolor = 3 ' red Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P323")) Is Nothing Then ColorCells For Each c In Target Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 'shrug Case 1 icolor = 45 'light orange Case 2 icolor = 4 'bright green Case 3 icolor = 10 'green Case 4 icolor = 5 'blue Case 5 icolor = 48 'gray Case 6 icolor = 9 'dark red Case Is 6 icolor = 3 'red Case Else 'whatever End Select c.Interior.ColorIndex = icolor Next c End If End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
shading cells trouble
hi umm
i dont know if i was meant to replace anything with anything, so i just copied and pasted your code over mine it gives me a Run-Time error '91': Object variable or With block variable not set "Coza" wrote: Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P323")) Is Nothing Then if c.Value <"" then ' May speed up things a little ColorCells For Each c In Target Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 'shrug Case 1 icolor = 45 'light orange Case 2 icolor = 4 'bright green Case 3 icolor = 10 'green Case 4 icolor = 5 'blue Case 5 icolor = 48 'gray Case 6 icolor = 9 'dark red Case Is 6 icolor = 3 'red Case Else 'whatever End Select c.Interior.ColorIndex = icolor Next c End If end if End Sub "Mo2" wrote in message ... I'm not very good with scripting. this script automatically shades cells based on values that i enter. The script runs every time i edit a value in a cell. It checks about 300 rows in about 10 columns. The problem is, it takes very long. I dont really need to check every cell between the F column and P column. how do i specify this code to only update the cell shading of Cell F25:F324 , H25:H324, J25:J324, L25:L324, N25:N324, and P25:P324 OR if its possible, instead of updating up to the last row of the those columns specified, how about the code only checks up to the row i am currently working on? Another question i have is... Before i start, i know what row i will be going up to. can i specify this in some cell or pop up window, and have my script's range automatically update to that row? (instead of up to row 324)? TIA Sub ColorCells() Dim icolor As Integer Dim c As Range For Each c In Range("F25:F324", "P25:P323") Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 ' dark green Case 1 icolor = 45 'light orange Case 2 icolor = 4 ' bright green Case 3 icolor = 10 ' green Case 4 icolor = 5 ' blue Case 5 icolor = 48 ' gray Case 6 icolor = 9 ' dark red Case Is 6 icolor = 3 ' red Case Else icolor = 2 End Select c.Interior.ColorIndex = icolor Next c End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer Dim c As Range If Not Intersect(Target, Range("A25:P323")) Is Nothing Then ColorCells For Each c In Target Select Case c Case Is < 0 icolor = 3 'red Case 0 icolor = 51 'shrug Case 1 icolor = 45 'light orange Case 2 icolor = 4 'bright green Case 3 icolor = 10 'green Case 4 icolor = 5 'blue Case 5 icolor = 48 'gray Case 6 icolor = 9 'dark red Case Is 6 icolor = 3 'red Case Else 'whatever End Select c.Interior.ColorIndex = icolor Next c End If End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum of all cells with red shading | Excel Discussion (Misc queries) | |||
| Shading Cells | Excel Discussion (Misc queries) | |||
| Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
| Shading Cells | Excel Discussion (Misc queries) | |||
| Shading Cells | Excel Worksheet Functions | |||
Linear Mode
