Hey Julie,

Thanks for this. I tried the XLD Tools program however it only does the
changes for what is currently displayed - if you change the contents the
colour doesnt change as well.

Im not trying your worksheet_change and its working nicely (though a little
slow - but its a big spreadsheet). Can you change the cell colour as well
using this method or only the font colour?

Thanks,

Pauline

"JulieD" wrote:

Hi Beth

couple of options:

- John McGimpsey has an article on doing up to 6
www.mcgimpsey.com/excel/conditional6.html

- Bob Phillips & Frank Kabel have developed an excel add-in
http://www.xldynamic.com/source/xld.....Downlaod.html

- or you can use some code in the worksheet_change event (paste in the
"sheet module" of the sheet -
right mouse click on the sheet tab and choose view / code you should see on
the top left of the VBE window your file name in bold (if not try view /
project explorer) and the sheet that you were on selected ... that's the
"sheet module" ... if the wrong sheet is selected then just double click on
the correct one on the right you should see some white space - copy & paste
the code in
there -

assuming you want the conditional formatting to work on cell B6
---
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Range("B6")) Is Nothing Then
With Target
Select Case .Value
Case 1: Range("B6").Font.ColorIndex = 4
Case 2: Range("B6").Font.ColorIndex = 3
Case 3: Range("B6").Font.ColorIndex = 0
Case 4: Range("B6").Font.ColorIndex = 6
Case 5: Range("B6").Font.ColorIndex = 13
Case 6: Range("B6").Font.ColorIndex = 46
Case 7: Range("B6").Font.ColorIndex = 11
Case 8: Range("B6").Font.ColorIndex = 7
Case 9: Range("B6").Font.ColorIndex = 55
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

--- this turns the font of B6 a different colour depending on what value
(between 1 & 9) is entered in the cell. If you want to use text, replace
the numbers (1 - 9) with the text in quotes (e.g. "cat", "dog" etc)

Hope this helps
Cheers
JulieD


"Beth H" wrote in message
...
I'm aware that excel 2000 limits conditional formatting to 3 conditions. I
would like to use 8. I have a column with data validation applied in which
the user can only select and enter 1 of 8 values. If they enter "DW", then
fill color = blue; if "O" then fill color = purple; if "AB", then fill
color
= orange; if "ZY" then fill color = gray.... etc. How can I get around the
limit of 3 conditional formatting conditions?