conditonal formatting

[Gail]

How do I get more than 3 conditional formats in a excel
worksheet? I need about 10. I would like to color code
services that a client is requesting so that our staff
can easily look at the the list and call their clients.
Or is there another way to go about this? I am using
Excel 2002.

Thanks
Gail

 

[JE McGimpsey]

10 requires using VBA event macro. To apply formats to, say, A1:A10, put
something like this in your worksheet code module (right-click on the
worksheet tab and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A10")) Is Nothing Then
Select Case LCase(Target.Text)
Case "service 1"
.Interior.ColorIndex = 3
Case "service 2"
.Interior.ColorIndex = 4
Case "service 3"
.Interior.ColorIndex = 5
Case "service 4"
.Interior.ColorIndex = 6
Case "service 5"
.Interior.ColorIndex = 7
Case "service 6"
.Interior.ColorIndex = 8
Case "service 7"
.Interior.ColorIndex = 9
Case "service 8"
.Interior.ColorIndex = 10
Case "service 9"
.Interior.ColorIndex = 11
Case "service 10"
.Interior.ColorIndex = 12
Case Else
.Interior.ColorIndex = xlColorIndexNone
End Select
End If
End With
End Sub


Substitute your Ranges, Services, and colors

 

[Gail]

Thank you for your help it is working great, but know how
do I pick my own colors, and how do I make the font in
certain cells white since some of the colors are two dark
to read black text.

VBA is a new function for me and would like to know more,
have any suggestons on good website that will explain
more vba functions? I am likely the versitily of this.

Thanks again
Gail

-----Original Message-----

 

[SidBord]

There are three good techniques for learning Visual Basic
programming: 1) buy a good reference book, 2) let the
system write a macro for you, and 3) make use of the VB
Help facility.
1) I use a book called "Microsoft Office Excel 2003 Power
Programming with VBA" (over 1000 pages thick) by John
Walkenbach (see if you can get a good price on EBay or
Amazon.com)
2) While in Excel, click TOOLS->MACRO->Record New Macro,
then start entering what you want to do manually through
the keyboard. When finished, go to TOOLS->MACRO->Stop
Recording. Then go into the macro editor and look for
Macro1 (or whatever name is assigned), and study what you
see there. There will be more code than you want or need
usually, but you can learn some good programming statements
that way.
3) While in the macro editor, put your cursor on some part
of a statement that you need info on, then press F1 to get
help on that item.
Remember you learn by doing.

 

[Gord Dibben]

A good place to start is David McRitchie's site for getting started with VBA.

Note the links to other sites for tutorials at the bottom of the page.

Gord Dibben Excel MVP

 

[JE McGimpsey]

Thank you for your help it is working great, but know how
do I pick my own colors, and how do I make the font in
certain cells white since some of the colors are two dark
to read black text.

In the Visual Basic Editor, check Help on ColorIndex - there's a palette
that shows what each (default) colorindex number is.

You can change font colors by using

.Font.ColorIndex

instead of (or in addition to)

.Interior.Colorindex

VBA is a new function for me and would like to know more,
have any suggestons on good website that will explain
more vba functions? I am likely the versitily of this.

Take a look at David McRitchie's "Getting Started with Macros and User
Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Gail]

I used this VB and it was working like a charm and then
one day it just stopped working. The only thing I can
think of is that I deleted several rows but it was within
the specific range I had in the VB code and now my VB
will not work. Any suggestions to what is going wrong?
Here is the code that I am using

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("A1:A600")) Is
Nothing Then
Select Case LCase(Target.Text)
Case "pick from list"
.Interior.ColorIndex = 3
Case "speech assessment"
.Interior.ColorIndex = 43
Case "speech therapy"
.Interior.ColorIndex = 35
Case "auditory processing assessment"
.Interior.ColorIndex = 41
.Font.ColorIndex = 2
Case "auditory processing therapy"
.Interior.ColorIndex = 37
Case "dyslexia assessment (child)"
.Interior.ColorIndex = 45
Case "dyslexia therapy (child)"
.Interior.ColorIndex = 40
Case "dyslexia assessment (adult 18
+ )"
.Interior.ColorIndex = 54
.Font.ColorIndex = 2
Case "dyslexia therapy (adult 18 + )"
.Interior.ColorIndex = 39
Case "accomodations"
.Interior.ColorIndex = 6
Case Else
.Interior.ColorIndex =
xlColorIndexNone
End Select
End If
End With
End Sub

 

[JE McGimpsey]

Put a breakpoint on the "If .Count > 1 ..." line, switch back to XL,
and make an entry in A1. Does the event fire? If so, step through the
code and see what it's doing right or wrong...