ty..that works..
but there is still a problem (err... "inconvinience" rather.)

i might send this script to "non-excel literate folks" (like me ..but i
kinda figured it a little)

anywho... is there an easier way to activate this script than having to go
in "View Code" ?


a Second problem i'm having:

my first column has existing data
my 2nd column has data i manually input.
and every other column after that has formulas, copied down, which
auto-update cell values, based on what i enter in the B column.

This code for shading cells works for the 2nd column.
your code works for every other cell.
BUT

if i edit a value in column B (which changes stuff in teh other columns)
the other columns's values dont update their color.

its a real pain having to 'run' the script every single time i edit something.
can i have it continuously running somehow?

thanks in advance



"Vergel Adriano" wrote:

The code that you have fires only whenever a cell or range is modified in the
spreadsheet. It will not 'process' any of the cells with existing data
unless they are modified the user. To apply the color scheme to cells with
data, try running a code like this

Sub ColorCells()
Dim icolor As Integer
Dim c As Range
For Each c In Range("A25:P344")
Select Case c
Case Is < 0
icolor = 3
Case 0
icolor = 51
Case 1
icolor = 45
Case 2
icolor = 4
Case 3
icolor = 10
Case 4
icolor = 5
Case 5
icolor = 48
Case 6
icolor = 9
Case Is 6
icolor = 3
Case Else
icolor = 2
End Select

c.Interior.ColorIndex = icolor
Next c
End Sub

After that your Worksheet_Change code should take care of future changes.
As for changing the data a 2nd or 3rd time, I did not get any error.

To take care of when multiple cells are updated, try this slighlty modified
version of your code:

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

If Not Intersect(Target, Range("A25:P344")) Is Nothing Then
For Each c In Target
Select Case c
Case Is < 0
icolor = 3
Case 0
icolor = 51
Case 1
icolor = 45
Case 2
icolor = 4
Case 3
icolor = 10
Case 4
icolor = 5
Case 5
icolor = 48
Case 6
icolor = 9
Case Is 6
icolor = 3
Case Else
icolor = 2
End Select

c.Interior.ColorIndex = icolor
Next c
End If

End Sub



--
Hope that helps.

Vergel Adriano


"Mo2" wrote:

ok, thanks to this here site:
http://www.ozgrid.com/VBA/excel-cond...ting-limit.htm
and help from u guys, i've figured out how to shade cells based on conditions.
(I couldnt use Conditional Format becuz there was more than 3 conditions)

anyhow...
this piece of code works as it should.
when a cell value is "1", its an orange color
when a cell value is less than 0, the cell turns red.

my problem is... this code doesnt apply to cells that already have "1" as a
value, and so forth (the other cases mentioned below)

why doesn't it ? and how i would i make it?

also (if it isn't resolved by the first questions resolve)

it gives me an error when i change a cell's value a 2nd or 3rd time
(i.e. from value "-3" (cell color turns red) to "4" (gives an error)

error also occurs when i delete a cell or paste something over multiple cells.

here's my code..... thanks in advance if you can help me out



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

If Not Intersect(Target, Range("A25:P344")) Is Nothing Then
Select Case Target
Case Is < 0
icolor = 3
Case 0
icolor = 51
Case 1
icolor = 45
Case 2
icolor = 4
Case 3
icolor = 10
Case 4
icolor = 5
Case 5
icolor = 48
Case 6
icolor = 9
Case Is 6
icolor = 3
Case Else
icolor = 2
End Select

Target.Interior.ColorIndex = icolor
End If

End Sub