if condition is met, shade cell [WORKS] but if value changes: erro

[Mo2]

ok, thanks to this here site:
http://www.ozgrid.com/VBA/excel-conditional-formatting-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("A25344")) 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

 

[Vergel Adriano]

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("A25344")
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("A25344")) 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

 

[Mo2]

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]

Unless you declared the Sub as Private, you should be able to run it by going
to Tools->Macro->Macros or by pressing Alt-F8. You would see a list of
subroutines and you can run them from there.

As for the second problem you're having, as long as you're working with a
relatively small range, you can persist the color changes by refreshing the
colors everytime a cell in the range is changed by calling the ColorCells
sub. i.e.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A25344")) Is Nothing Then
ColorCells
End If
End Sub

 

[Mo2]

wow
ok, it did work..
but.. As you said, its better for smaller ranges.
and for my "A25324" range..it takes a few seconds to apply.

QUESTION 1:
is it possible to apply this cell shading code to just the following cells:
D1 through D323
F1 through F323
H1 through H323
J1 through J323
L1 through L323
N1 through N323
P1 through P323
hopfeully, that will speed things up.

QUESTION 2:

Is there another way to activate the macro?
for instance, the click of the word "ColorTheCells" in cell A1.
and a way to deactivate the macro?

(i'm trying to make this template sheet as little work as possible for ppl
who've never used excel before)