First, you can specify the Fill color--choose it on the pattern tab.

Second, if you're gonna stick with format|conditional formatting, you're stuck
with 3 conditions.

But you could use a worksheet that actually changes the font color and/or the
fill color the way you want.

If you want to try....

rightclick on the worksheet tab that should have this behavior. Select view
code and paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myChars As Variant
Dim myColors As Variant
Dim myFills As Variant
Dim iCtr As Long
Dim FoundAMatch As Boolean

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

myChars = Array("PM", "UE", "BE")
myColors = Array(3, 6, 7)
myFills = Array(44, 23, 18)

If UBound(myChars) < UBound(myColors) Then
MsgBox "error #1. Contact Davison(?)"
Exit Sub
End If

If UBound(myChars) < UBound(myFills) Then
MsgBox "error #2. Contact Davison(?)"
Exit Sub
End If

With Target
FoundAMatch = False
For iCtr = LBound(myChars) To UBound(myChars)
If InStr(1, .Value, myChars(iCtr), vbTextCompare) 0 Then
FoundAMatch = True
Exit For
End If
Next iCtr

If FoundAMatch = True Then
.Font.ColorIndex = myColors(iCtr)
.Interior.ColorIndex = myFills(iCtr)
End If
End With

End Sub


This looks for changes in column A (only).

Change this to the range you want to use:
If Intersect(Target, Me.Range("a:a")) Is Nothing Then Exit Sub

I didn't care about upper/lower case (I used vbtextcompare). You can change
that to what you want.

You'll want to add your strings to search for to this line:
myChars = Array("PM", "UE", "BE")

And colors are specific to workbooks. My colors may not match yours. I chose
these things pretty much at random.

myColors = Array(3, 6, 7)
myFills = Array(44, 23, 18)

I'd record a macro when I changed the font color and fill color (6 times) to get
the numbers I want. Then just fix those arrays.

The event that I used was worksheet_change. If the text changes as the result
of a formula, then this won't work--but there are other events
(worksheet_calculate) that might do what you want.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

If you want to read more about these kinds of events:

Chip Pearson's site:
http://www.cpearson.com/excel/events.htm

David McRitchie's site:
http://www.mvps.org/dmcritchie/excel/event.htm



nosivad wrote:

Thanks, that helped. It only applies to font color though and being
restricted to 3 sucks. Ideally I'd like to be able to change the
background color too, but I'll take what I can get. :)

Is there no way to manually create a format that will format cells, so
that I'm not limited to 3 (via conditional formatting)?

Something like
=IF(ISNUMBER(FIND("BE",A1:E100))) FONTCOLOR=yellow
=IF(ISNUMBER(FIND("UE",A1:E100))) FONTCOLOR=purple
etc

--
nosivad
------------------------------------------------------------------------
nosivad's Profile: http://www.excelforum.com/member.php...o&userid=31434
View this thread: http://www.excelforum.com/showthread...hreadid=511265

--

Dave Peterson