Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
Ok, so I need to use Excel to quickly indicate who owns a task by the 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 whole sheet? Conditional formatting only seems to apply to the text, and when I tried to apply the example formula in Excel’s help file it wouldn’t work. =IF(ISNUMBER(FIND("BE",A2)),"TRUE", "FALSE") I’m a bit of a noob, so sorry if the answer this seems obvious, but this has been driving me crazy all day. -- nosivad ------------------------------------------------------------------------ nosivad's Profile: http://www.excelforum.com/member.php...o&userid=31434 View this thread: http://www.excelforum.com/showthread...hreadid=511265 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
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). nosivad wrote: Ok, so I need to use Excel to quickly indicate who owns a task by the 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 whole sheet? Conditional formatting only seems to apply to the text, and when I tried to apply the example formula in Excel’s help file it wouldn’t work. =IF(ISNUMBER(FIND("BE",A2)),"TRUE", "FALSE") I’m a bit of a noob, so sorry if the answer this seems obvious, but this has been driving me crazy all day. -- 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
But you could use a worksheet EVENT that actually changes the font color
and/or the fill color the way you want. Dave Peterson wrote: 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Conditional cell background formatting, based on cell content
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!! -- nosivad ------------------------------------------------------------------------ nosivad's Profile: http://www.excelforum.com/member.php...o&userid=31434 View this thread: http://www.excelforum.com/showthread...hreadid=511265 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Cell Shading (based on the shading of other cells) | Excel Worksheet Functions | |||
Conditional Formatting if any value in the cell | Excel Worksheet Functions | |||
Why won't my conditional formatting display in the cell | Excel Discussion (Misc queries) | |||
conditional formatting blank cell | Excel Discussion (Misc queries) | |||
GET.CELL | Excel Worksheet Functions |