More than 3 conditional formatting conditions?

O

oodam

I'm using Excel 2000, and I need to program a row of cells to tur
certain colours based on 5 possible words being input in a given cell.
So, basically:
If V is in C1, I want that entire row to turn red
If W is in C1, I want that entire row to turn blue,
and so on for five (or six) different possibilities.

Everything I've read says that every pre-2007 version of Excel onl
allows three such conditions. Am I out of luck, or is there some wa
around that
 
M

Mallycat

oodam said:
Am I out of luck, or is there some way around that?
Click to expand...

The way around it is to use VBA to code this for you. You get some
code to run on the change event when something changes in the C column.
Then get the code to colour the row for you.

I have created this for you and placed it here.
http://members.optusnet.com.au/~allington65/Files/colour_rows.xls

The code is below. You can add extra lines in the select case section
to add new letters that cause different colours.

Matt

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then 'only run if column C changes
For Each Cell In Target
myData = Cell.Value
Rows(Cell.Row).Select

Select Case myData
Case "v"
myColour = 3 ' red
Case "w"
myColour = 41 'blue
Case "x"
myColour = 4 'green
Case Else
Selection.Interior.ColorIndex = xlNone
GoTo ExitHere:
End Select

With Selection.Interior
..ColorIndex = myColour
..Pattern = xlSolid
..PatternColorIndex = xlAutomatic
End With

Next Cell
ExitHere:
Target.Offset(1, 0).Select
End If
End Sub
 
O

oodam

Thanks, that's amazingly helpful.
One more question: how do I get a list of the colour codes so I can us
it for other colours
 
O

oodam

I think I figured that out (assuming that it's this list:
http://www.mvps.org/dmcritchie/excel/colors.htm). I do have one other
question:
Is there any way to have it look for part of the contents of a cell: ie
have it look for "dog," in cells containing "red dog," "brown dog," and
"black dog." I know with regular formulas this can be done by using
asterisks, but that doesn't seem to work with this.
 
Top