Conditional Formatting is limited to the 3 options. But you can simulate
more using VBA coding. The sample below is from an actual project I did
where we needed more options based on the text content of the cells. Change
the tests for conditions to meet your needs, or if you have problems with
that, post and I'll try to help. This has to go into each sheet's _Change
event as shown - each sheet meaning each sheet where you want this
conditional formatting to take place.

The Application.Intersect() function tests to see if the currently selected
cell (Target) intersects with an area on the worksheet that you define.
That's defined in the code as variable CellsToExamine, change that range for
your setup. If you wanted all of column A, you could use A1:A65536
(possibly simply A:A but I've never used it that way for an .Intersect
operation).

The code shows how to change both the background color of the cell and how
to change the font color. If you need to change to other colors or do things
like make the font bold, simply record macros doing what you need to do and
follow that model to making changes. Making the font Bold is easy also:
Target.Font.Bold = True
will make it bold,
Target.Font.Bold = False
will make it normal.

This page will tell you how to get into the VB Editor and add code to a
worksheet, which you need to do for this:
http://www.jlathamsite.com/Teach/WorksheetCode.htm

Private Sub Worksheet_Change(ByVal Target As Range)
'any time a change to a cell is made on this worksheet
'this event takes place
'This code checks to see if the cell where a change
'took place is within a certain defined group of cells
'if it did take place within the group, the current
'contents of the changed cell is examined and the
'fill color of the cell is set based on it
'
'the end effect it to give you an extended
'Conditional Format function
'
Dim iSect As Range
Dim CharRangeName As String
Dim CellsToExamine As String

CellsToExamine = "A1:A100" ' change as needed
'for just 1 entire column
Set iSect = Application.Intersect(Range(Target.Address), _
Range("CellsToExamine"))
If (iSect Is Nothing) Then
Exit Sub
End If
'Excel cells are not case sensitive, but
'VB code is, so we make sure that we make
'valid comparisons
'TRIM removes leading/trailing white space
'UCASE converts lowercase characters to uppercase
Select Case Trim(UCase(iSect.Text))
Case Is = "FI"
Target.Interior.ColorIndex = 4 ' Bright Green
Target.Font.ColorIndex = 9 ' Dark Red
Case Is = "LI"
Target.Interior.ColorIndex = 6 ' Bright Yellow
Case Is = "PI"
Target.Interior.ColorIndex = 45 ' Orange
Case Is = "NI"
Target.Interior.ColorIndex = 3 ' Bright Red
Case Else
'if anything other than those 4, no color
Target.Interior.ColorIndex = xlNone ' No Fill
Target.Font.ColorIndex = xlAutomatic ' standard
End Select
Set iSect = Nothing ' release resources
End Sub

"brillopad52" wrote:

I have a column that I need to apply 5 conditonal formats to, can that be
done, and if so how? Thanks