Conditional Formatting
 

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

Conditional Formatting
 

You can have as many as you want with macros. follow the link below to get up
to 6

http://www.mcgimpsey.com/excel/conditional6.html

Mike

"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

Conditional Formatting
 

Depending on what data you are using there are ways

http://www.mcgimpsey.com/excel/conditional6.html


or get an add-in

http://www.xldynamic.com/source/xld.....Download.html

or get the next version of Excel which has no limits except system memory


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Bob Smith" wrote in message
ink.net...
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

Conditional Formatting
 

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

Conditional Formatting
 

Thanks for the suggestions folks. I've tried Frank Cabel & Bob Phillips
CFPlus add in, and it's great file. Only thing is that it won't allow for
global formatting in a column of cells for more than one sheet, and it would
take forever to set up 8 formatting instructions for each workbook, going
sheet by sheet.

Bob

"Peo Sjoblom" wrote in message
...
Depending on what data you are using there are ways

http://www.mcgimpsey.com/excel/conditional6.html


or get an add-in

http://www.xldynamic.com/source/xld.....Download.html

or get the next version of Excel which has no limits except system memory


--


Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
(Remove ^^ from email)


"Bob Smith" wrote in message
ink.net...
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

Conditional Formatting
 

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.

As you can see, I need some color numbers and addition in on background
pattern colors.

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?

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

Conditional Formatting
 

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

Conditional Formatting
 

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

Conditional Formatting
 

Thanks for the replies Gord. I will follow up on your suggestions when I
find some time tomorrow and let you know how it works out.

Bob

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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