Conditional formatting for more than 3 conditions

[joeshow]

Could someone shed some light on this subject for me please.
What I have is data is Column I & J from rows 4 thru 45 (I4:J45).
need the cell to be colored in by the data that is entered in the box.

THis is kind of the bare bones of the arguement:
Condition 1
If Cell Value > 0.1 then Green cell
Condition 2
If cell Value < -0.1 then Red cell
Condition 3
If cell Value between 0.1 & -0.1 then Yellow cell
Condition 4
If cell Value = "mid range" then white (no color)

I have been doing some research and found select case statements t
probably fit the best from what im asking but i havent been able t
create the proper syntax.
Please help anyone that may have an idea how to do this. thanks an
very much appreciate it

 

[Frank Kabel]

Hi
see your reply in public.excel

P.S: please don't multipost

 

[joeshow]

im sorry but i dont understand where you are referring to
public.excel??

 

[Frank Kabel]

Hi
you also posted this question to the NG 'Microsoft.public.excel'. Find
below a repost:

------------------
Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.

If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6 different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the entry in cell A1:A100 based on its value:

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 > 0.2: .Interior.ColorIndex = 3
Case is < 0.2: .Interior.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

Adapt this to your needs