Hi Mompmc!
First question:
You really need VBA code for more than 3 conditions. Here's an example
that you can adapt that was posted by Ron Rosenfeld on January 4th:
Sub PerCentChg()
Dim c As Range
For Each c In Selection
c.NumberFormat = "0.000"
Select Case Application.WorksheetFunction.Round(c.Value, 3)
Case Is >= 1.255
c.Interior.Color = vbWhite
c.Font.Color = vbBlack
Case 1.236 To 1.254
c.Interior.Color = vbCyan
c.Font.Color = vbBlack
Case 1.215 To 1.235
c.Interior.Color = vbMagenta
c.Font.Color = vbWhite
Case 1.201 To 1.214
c.Interior.Color = vbBlue
c.Font.Color = vbWhite
Case 1.18 To 1.2
c.Interior.Color = vbYellow
c.Font.Color = vbBlack
Case 1.166 To 1.179
c.Interior.Color = vbGreen
c.Font.Color = vbBlack
Case 1.155 To 1.165
c.Interior.Color = vbRed
c.Font.Color = vbWhite
Case 1.131 To 1.154
c.Interior.Color = vbBlack
c.Font.Color = vbWhite
Case 1.11 To 1.3
c.Interior.Color = vbBlack
c.Font.Color = vbWhite
Case Else
c.Interior.ColorIndex = xlNone
c.Font.Color = vbBlack
End Select
Next c
End Sub
Second question. Again, you need VBA.
Here's a solution I posted a week ago to a similar question:
This is one of those features that that we have not yet been blessed
with in any version of Excel.
But here is some code that is really nasty:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("MyFlashCell")) Is Nothing Then Exit Sub
Dim n As Integer
Dim NextTime As Date
If Range("MyFlashCell").Value > 7 Then
For n = 1 To 5
With Range("MyFlashCell").Font
If .ColorIndex = 2 Then .ColorIndex = 3 Else .ColorIndex = 2
End With
With Range("MyFlashCell").Interior
If .ColorIndex = 3 Then .ColorIndex = 2 Else .ColorIndex = 3
End With
Application.Wait Now + TimeValue("00:00:01")
Next
End If
With Range("MyFlashCell")
..Font.ColorIndex = 3
..Interior.ColorIndex = 2
End With
End Sub
It goes in the Sheet module, it sucks processing time and creates a
delay of 5 seconds on every recalculation if MyFlashCell exceeds the
value of 7.
Don't blame me if co-workers perform surgical operations on you
without anaesthetic.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
