5 conditions

[Guest]

I am unable to create 5 conditions on a spreadsheet
Using macros - I I have used the following formula

Private Sub Worksheet_Change(ByVal Target As Excel.Range
If Not Intersect(Target, Range("AF13")) Is Nothing The
With Range("AF13"
If IsNumeric(.Value) The
Select Case .Valu
Case Is > 1.
.Interior.ColorIndex = 8 'blu
Case Is >=
.Interior.ColorIndex = 4 'gree
Case Is >= 0.9
.Interior.ColorIndex = 46 'orang
Case Is =
.Interior.ColorIndex = 2 'whit
Case Is > 0, Is < 0.9
.Interior.ColorIndex = 3 're
End Selec
Els
.Interior.ColorIndex = xlColorIndexNon
End I
End Wit
End I
End Su

however it only permits for a cell to change color after you type the number in and not when it is updated automatically.
What I am looking for is for a range of cells to be updated automatically when there is a change

for example in one cell - AA1 - (what I am looking for is for a range of cells however for the example) - the value of the cell AA1 (due to the vlook up function) goes from being 1 to .94 - due to this change in value in this cell - another cell -A1- whose condition is based on the value of this cell - changes color...whilst maintaining the 5 condition

how is this done
please help...suggestions

 

[Frank Kabel]

Hi
for this you can use the worksheet_calculate event. Not
tested but try:

Private Sub Worksheet_Calculate ()
With me.Range("AF13")
If IsNumeric(.Value) Then
Select Case .Value
Case Is > 1.1
.Interior.ColorIndex = 8 'blue
Case Is >= 1
.Interior.ColorIndex = 4 'green
Case Is >= 0.95
.Interior.ColorIndex =
46 'orange
Case Is = 0
.Interior.ColorIndex = 2 'white
Case Is > 0, Is < 0.95
.Interior.ColorIndex = 3 'red
End Select
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
End Sub

-----Original Message-----
I am unable to create 5 conditions on a spreadsheet.
Using macros - I I have used the following formula:


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("AF13")) Is Nothing Then
With Range("AF13")
If IsNumeric(.Value) Then
Select Case .Value
Case Is > 1.1
.Interior.ColorIndex = 8 'blue
Case Is >= 1
.Interior.ColorIndex = 4 'green
Case Is >= 0.95
.Interior.ColorIndex = 46 'orange
Case Is = 0
.Interior.ColorIndex = 2 'white
Case Is > 0, Is < 0.95
.Interior.ColorIndex = 3 'red
End Select
Else
.Interior.ColorIndex = xlColorIndexNone
End If
End With
End If
End Sub

however it only permits for a cell to change color after

you type the number in and not when it is updated
automatically.

What I am looking for is for a range of cells to be

updated automatically when there is a change:

for example in one cell - AA1 - (what I am looking for is

for a range of cells however for the example) - the value
of the cell AA1 (due to the vlook up function) goes from
being 1 to .94 - due to this change in value in this cell -
another cell -A1- whose condition is based on the value
of this cell - changes color...whilst maintaining the 5
conditions