Conditional cell background formatting, based on cell content

N

nosivad

Ok, so I need to use Excel to quickly indicate who owns a task by th
cells background color. I have 6 text values (peoples initials: e.g
PM, UE, BE etc), that will appear in a cell with other text.

i.e.
BE – 4 hours work left on toe nail clipping task

How can I change a cells background color (and text too if possible
based on text that might appear and apply this formula to the whol
sheet?

Conditional formatting only seems to apply to the text, and when
tried to apply the example formula in Excel’s help file it wouldn’
work. =IF(ISNUMBER(FIND("BE",A2)),"TRUE", "FALSE")

I’m a bit of a noob, so sorry if the answer this seems obvious, bu
this has been driving me crazy all day
 
D

Dave Peterson

You could use:

=IF(ISNUMBER(FIND("BE",A2)),TRUE,FALSE)
or more simply:
=ISNUMBER(FIND("BE",A2))

But with format|conditional formatting, you'll only get three colors (plus the
default color).
 
N

nosivad

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
 
D

Dave Peterson

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
 
D

Dave Peterson

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

nosivad

Awesome!

Pasting that code in worked great. I was able to add more user id's
into the array and assign them colors, plus I expanded the formula to
apply to all the columns I needed. Perfect!

Thanks Dave!!
 
Top