shading cells trouble

[Guest]

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 P25324

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", "P25323")
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("A25323")) 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

 

[Coza]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
Dim c As Range

If Not Intersect(Target, Range("A25323")) 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

 

[Guest]

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