Color Coding For 5 Separate Ranges

  • Thread starter Thread starter Libby
  • Start date Start date
L

Libby

Hi. I'm new to Excel and need to either color-code the
contents of a cell, or indicate in the next cell what the
color should be. Example: If the number is <1500 or
2200, it should be red, if it's between 1600 and 1700 and
Click to expand...
if it's between 1900 and 2000, it should be yellow. If
it's between 1700 and 1900, it should be green. I've
tried Conditional Formatting, but only have 3 conditions,
tried naming a label with 1600-1700,1900-2000, etc. as =,
but anything following the comma doesn't work. Tried
several =IF statements, but more than 1 produces a value
of FALSE and not a color indicator (ie, Y,G,R). Can
someone help me?

Thanks very much.
 
Hi Libby!

Try Google Searching on "More than 3 conditional formats". You'll find
plenty of code examples of how to do it.

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

--
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.
 
Libby,

You need VBA.

Here is a worksheet event that does what you require, but you will need
to adjust the criteria. Put this code in the worksheet code
module (right-click on the sheet tab name, select View Code from the menu,
and paste the code in)

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1:H100")) Is Nothing Then
With Target
Select Case True
Case .Value >= 1500 And .Value <= 1600:
.Interior.ColorIndex = 45 'orange
Case Value > 1600 And .Value <= 1700:
.Interior.ColorIndex = 6 'Yellow
Case Value > 1700 And .Value <= 1900:
.Interior.ColorIndex = 10 'Green
Case Value > 1900 And .Value <= 2000:
.Interior.ColorIndex = 6 'Yellow
Case Else:
.Interior.ColorIndex = 3 'Red
End Select
End With
End If

ws_exit:
Application.EnableEvents = True

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Libby

If you mean 5 in a cell then the code below is an example of VBA CF in a
worksheet. Adjust to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("A:A"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is <= 0: Num = 10 'green
Case 0 To 5: Num = 1 'black
Case 5 To 10: Num = 5 'blue
Case 10 To 15: Num = 7 'magenta
Case 15 To 20: Num = 46 'orange
Case Is > 20: Num = 3 'red
End Select
'Apply the color
rng.Font.ColorIndex = Num
Next rng
End Sub

Gord Dibben Excel MVP
 
This group is terrific. A million thanks,
Libby.
-----Original Message-----
Hi Libby!

Try Google Searching on "More than 3 conditional formats". You'll find
plenty of code examples of how to do it.

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

--
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.



.
Click to expand...
 
Hi Libby!

Thanks for thanks is always appreciated. I posted Ron's approach
because it was so easily adaptable to problems.

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