Bob
Here is something else for you to peruse. Rather long-winded but gives you a
method of choosing font and background color for values.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastrow As Long
Dim Values As Variant
Dim cell As Variant
Dim Codes As Range
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Set Values = Range("A1:M" & lastrow)
For Each cell In Values
If cell.Value 0 And cell.Value <= 100 Then
With cell
.Interior.ColorIndex = 1 'black BG
.Font.ColorIndex = 2 'white Font
End With
ElseIf cell.Value 100 And cell.Value <= 200 Then
With cell
.Interior.ColorIndex = 1 'black BG
.Font.ColorIndex = 6 'yellow Font
End With
ElseIf cell.Value 200 And cell.Value <= 300 Then
With cell
.Interior.ColorIndex = 5
.Font.ColorIndex = 6
End With
End If
Next cell
End Sub
Note: if no BG color rquired for a value, just eliminate that line of code.
Gord
On Sat, 30 Dec 2006 15:01:27 -0800, Gord Dibben <gorddibbATshawDOTca wrote:
See in-line responses
On Sat, 30 Dec 2006 21:26:23 GMT, "Bob Smith"
wrote:
Hi Gord,
Thanks for the reply. I'm not all that conversant with actual coding, so
bear with me. I need to do a little more than what's listed below, with the
following ranges.
Case 10,000 To 249,999 clr = 10
Case 250,000 To 749,000 clr = 3
Case 750,000 To 1,499,999 clr = ?? (purple)
Case 1,5000,000 To 2,999,999 clr = ?? (white) Also need a black background
pattern
Case 3,000,000 To 5,999,999 clr = 5
Case 6,000,000 To 11,999,999 clr = ?? (dark red or brown)
Case 12,000,000 To 24,999,999 clr = ?? (yellow) Also need a black background
pattern.
Case 25,000,000 To 200,000,000 clr = 1 Also need a yellow background
I would use the regular CF to color the background for the three conditions you
state and the code for the font colors.
Unless someone jumps in and shows us how to have two color conditions per case.
One for the font color and one for the background.
As you can see, I need some color numbers and addition in on background
pattern colors.
See David McRitchie's site for a listing of the colorindex numbers.
http://www.mvps.org/dmcritchie/excel/colors.htm
Than once the above is corrected and replaces your coding below, do I insert
this code into *each* VBE worksheet, and if so, where? At the end of current
coding listed?
If you have multiple worksheets that require the code you could change the line
Private Sub Worksheet_Change(ByVal Target As Range) to the following which will
work with all sheets in the workbook.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
To enter the Thisworkbook Module, right-click on the Excel logo left of "File"
on the menu bar.......or Excel logo at left end of title bar if window is not
maximised.
Select "View Code" and copy paste into that module.
Gord
Thanks for any additional help you can provide.
Bob
"Gord Dibben" <gorddibbATshawDOTca wrote in message
. ..
Bob
You could download CFPlus add-in from Bob Phillips'
site...............allows up
to 30 choices.
http://www.xldynamic.com/source/xld.....Download.html
Or stick this in your sheet module....adjust/add ranges to suit.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim clr 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: clr = 10 'green
Case 0 To 5: clr = 1 'black
Case 5 To 10: clr = 5 'blue
Case 10 To 15: clr = 7 'magenta
Case 15 To 20: clr = 46 'orange
Case Is 20: clr = 3 'red
End Select
'Apply the color
rng.Font.ColorIndex = clr
Next rng
End Sub
To install the code, right-click on the sheet tab and "View Code".
Copy/paste into that sheet module.
Gord Dibben MS Excel MVP
On Sat, 30 Dec 2006 19:35:06 GMT, "Bob Smith"
wrote:
Another question for you mavens. I'd like to use conditional formatting to
color cell fonts s which are within a certain range. As it stands now, I
see
I can only set three sets of ranges.
Is there a way to tweak the Conditional Formatting feature to go to at
least
7 sets of ranges?
Is there any other solution any of you folks have?
TIA,
Bob