Conditional formatting for more than 3 conditions

  • Thread starter Thread starter joeshow
  • Start date Start date
J

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
 
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
 
Back
Top