Conditional format- several conditions and updates when the content changes

[Oslopelle]

Hi there all!
I'm new to vba but is in dire need of a vba macro that not only
supplies me with up to 30 conditional formats, but also "reads" the
result of a formula and changes the cell background color when the
result of the formula changes.
For example, if the simple sum formula =Sum(a1:b1) returned 2 (a1=1
and b1=1) and the conditional formatting made the cell backgrund
green, i would like a macro that can change the backgrund to red if
the sum formula returned 3 (a1=2and b1=1)

Can anyone help me?
I tried the vba code on ozgrid's page, but it only formats the cells
when I manually enters data, not when it is entered by a formula or if
the formula changes.
I tried this one that actually could read the result from the formula,
but didn't alter cell background it when the value of the formula
changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = 3: .Interior.ColorIndex = 7
Case Is = 1: .Interior.ColorIndex = 10
Case Is = 2: .Interior.ColorIndex = 16
Case Is = 4: .Interior.ColorIndex = 4
Case Is = 5: .Interior.ColorIndex = 6
Case Is = 0: .Interior.ColorIndex = 0
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I have 144 lookup formulas in a row (each refering to a 10 minute
portion of a day) for each of my 50 employees. The lookup formulas
return a number from 1 to 20 for a specified task or break or work-
station.
What I want is to color all cells for each work station in different
colors, breaks and meetings have their colors also.
The distribution of staff on all work-stations can be changed by the
manager each day.
If I got this system to work properly, it could be very flexible. I
could get the data I need to see who is most efficient on one
particular work-station, if I compared it with units sold in that time
period f.i.

Please please help!

 

[Bob Phillips]

Try the Calculate event, and loop through the whole of the target range.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Oslopelle]

Hi there!
Thanx for trying to help me, it's just that I'm a complete novice to
this. Your suggestions will probaby work wonderfully, but I don't have
the skill to write something like that....
Is there a description of this solution somewhere?
/Pelle

 

[Bob Phillips]

Replace that procedure with this one

Private Sub Worksheet_Calculate()
Dim Target As Range
For Each Target In Me.Range("A1:A100")
With Target
Select Case .Value
Case Is = 3: .Interior.ColorIndex = 7
Case Is = 1: .Interior.ColorIndex = 10
Case Is = 2: .Interior.ColorIndex = 16
Case Is = 4: .Interior.ColorIndex = 4
Case Is = 5: .Interior.ColorIndex = 6
Case Is = 0: .Interior.ColorIndex = 0
'etc.
End Select
End With
Next Target
End Sub


--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)