Cell Shading > 3 conditions

I have a spreadsheet that contains up to 6 different text strings. I'd
like to automatically change the text color or the cell shading
depending on the specific condition. For example, if the cell contains
"A", make the cell shading red. If it contains "B", change the font
color to red. Excel's conditional formatting limits me to 3 conditions.
How can I accomplish this objective?

I'm using Office 98 on a Mac, and worse, I'm not VBA smart.

Thanks.
rick


-- 
rabsparks
------------------------------------------------------------------------
rabsparks's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16491
View this thread: http://www.excelforum.com/showthread.php?threadid=278360

0
11/15/2004 7:08:08 PM
excel 39879 articles. 2 followers. Follow

4 Replies
621 Views

Similar Articles

[PageSpeed] 5

Hi
try the following addin:
http://www.xldynamic.com/source/xld.CFPlus.Download.html



--
Regards
Frank Kabel
Frankfurt, Germany

"rabsparks" <rabsparks.1fs1vy@excelforum-nospam.com> schrieb im
Newsbeitrag news:rabsparks.1fs1vy@excelforum-nospam.com...
>
> I have a spreadsheet that contains up to 6 different text strings.
I'd
> like to automatically change the text color or the cell shading
> depending on the specific condition. For example, if the cell
contains
> "A", make the cell shading red. If it contains "B", change the font
> color to red. Excel's conditional formatting limits me to 3
conditions.
> How can I accomplish this objective?
>
> I'm using Office 98 on a Mac, and worse, I'm not VBA smart.
>
> Thanks.
> rick
>
>
> --
> rabsparks
> ---------------------------------------------------------------------
---
> rabsparks's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=16491
> View this thread:
http://www.excelforum.com/showthread.php?threadid=278360
>

0
frank.kabel (11126)
11/15/2004 8:00:38 PM
One way:

CTRL-click the worksheet tab to enter the Visual Basic Editor (VBE). 
Paste one of these into the module that opens...

If the values are entered by the user:

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
        With Target
           If .Count > 1 Then Exit Sub
           If Not Intersect(.Cells, Range("A1:A5,B2:C3")) Is Nothing Then
                .Interior.ColorIndex = xlColorIndexNone
                .Font.ColorIndex = xlColorIndexAutomatic
                Select Case .Value
                    Case "A"
                        .Interior.ColorIndex = 3
                    Case "B"
                        .Font.ColorIndex = 3
                    Case "C"
                        .Interior.ColorIndex = 5
                    Case "D"
                        .Font.ColorIndex = 5
                    Case "E"
                        .Interior.ColorIndex = 6
                    Case "F"
                        .Font.ColorIndex = 6
                End Select
            End If
        End With
    End Sub

If the values are calculated:

    Private Sub Worksheet_Calculate()
        Dim rCell As Range
        For Each rCell In Range("A1:A5,B2:C3")
            With rCell
                .Interior.ColorIndex = xlColorIndexNone
                .Font.ColorIndex = xlColorIndexAutomatic
                Select Case rCell.Value
                    Case "A"
                        .Interior.ColorIndex = 3
                    Case "B"
                        .Font.ColorIndex = 3
                    Case "C"
                        .Interior.ColorIndex = 5
                    Case "D"
                        .Font.ColorIndex = 5
                    Case "E"
                        .Interior.ColorIndex = 6
                    Case "F"
                        .Font.ColorIndex = 6
                End Select
            End With
        Next rCell
    End Sub

Change the .Interior or .Font to suit. Change the range reference to 
suit.

While in the VBE, search for the ColorIndex property in Help. There's a 
palette there that will tell you what numbers to use for which colors.

In article <rabsparks.1fs1vy@excelforum-nospam.com>,
 rabsparks <rabsparks.1fs1vy@excelforum-nospam.com> wrote:

> I have a spreadsheet that contains up to 6 different text strings. I'd
> like to automatically change the text color or the cell shading
> depending on the specific condition. For example, if the cell contains
> "A", make the cell shading red. If it contains "B", change the font
> color to red. Excel's conditional formatting limits me to 3 conditions.
> How can I accomplish this objective?
> 
> I'm using Office 98 on a Mac, and worse, I'm not VBA smart.
0
jemcgimpsey (6723)
11/15/2004 8:13:01 PM
As a Mac user, he may not be able to easily view that page. The 
javascript on the xldynamic site breaks in the Safari browser. Mozilla 
(Firefox, Camino) is more tolerant. Of course, turning off javascript 
works, too.


In article <eL1MY20yEHA.908@TK2MSFTNGP11.phx.gbl>,
 "Frank Kabel" <frank.kabel@freenet.de> wrote:

> try the following addin:
> http://www.xldynamic.com/source/xld.CFPlus.Download.html
0
jemcgimpsey (6723)
11/15/2004 8:17:50 PM
Hi
forget this solution and use JE's code example. I just recognised that
you're using a MAC and unfortunately the addin will not run on this
platform!

--
Regards
Frank Kabel
Frankfurt, Germany

"Frank Kabel" <frank.kabel@freenet.de> schrieb im Newsbeitrag
news:eL1MY20yEHA.908@TK2MSFTNGP11.phx.gbl...
> Hi
> try the following addin:
> http://www.xldynamic.com/source/xld.CFPlus.Download.html
>
>
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "rabsparks" <rabsparks.1fs1vy@excelforum-nospam.com> schrieb im
> Newsbeitrag news:rabsparks.1fs1vy@excelforum-nospam.com...
> >
> > I have a spreadsheet that contains up to 6 different text strings.
> I'd
> > like to automatically change the text color or the cell shading
> > depending on the specific condition. For example, if the cell
> contains
> > "A", make the cell shading red. If it contains "B", change the font
> > color to red. Excel's conditional formatting limits me to 3
> conditions.
> > How can I accomplish this objective?
> >
> > I'm using Office 98 on a Mac, and worse, I'm not VBA smart.
> >
> > Thanks.
> > rick
> >
> >
> > --
> > rabsparks
>
> ---------------------------------------------------------------------
> ---
> > rabsparks's Profile:
> http://www.excelforum.com/member.php?action=getinfo&userid=16491
> > View this thread:
> http://www.excelforum.com/showthread.php?threadid=278360
> >
>

0
frank.kabel (11126)
11/15/2004 8:20:25 PM
Reply:

Similar Artilces:

help on cell formats
-------------------------------------------------------------------------------- thank you guys. but... may be i didnt explain clearly enough.... I have two columns... *H* and *I* ... in column *H *i'm entering a dollar amount.. $1,108.83.. once i hit enter, the *H* column should only show the dollar amount which is 1,108 and column *I* should only show the remaining cents which is 83 hope i explained better this time.. -- Joe@excel ------------------------------------------------------------------------ Joe@excel's Profile: http://www.excelforum.com/member.php?action=getin...

How to move Entourage X data to Entour. 2008 (in OS 10.6.3)
I have searched for a week an only find old scripts not applicable to Snow Leopard (Paul Berkowitz, Jolly roger, Brian Wainright). I can get none of these to work. MAybe I missed something. My initial data move with Entourage 2008 set up failed to move any Entourage X data into 2008. I was able to move my contacts into 2008 ( I forget now how I did that last week). For the last week plus I have been trying off and on to move my old mail into MSO Entourage 2008. With Brian Wainrights' script I was able to get my email into a folder on my Desk Top 'Mail Folders' (MBOX)...

Allow only one special character i a cell
Hi... Is there a way to allow only one special character i a cell? I use the validation on the Data-menu to allow only one character in a cell, but in this cell the only character allowed is a capital "X" Is it possble? -- Regards Zadig Galbaras -- Zadig Pick a cell, place an X in it, validation reference to that cell Data/Validation/List/pick cell/no drop down, no ignore blank Beege "Zadig Galbaras" <tresfjording@gmail.no> wrote in message news:%23o38vBMKGHA.1032@TK2MSFTNGP11.phx.gbl... > Hi... > Is there a way to allow only one special character i...

Lending Money #3
How does everyone track loans to others? Seems that the way to do it is to set up an asset account as a loan. What confuses me is how to record the transaction whereby you actually loan the money. For now, I've setup a new category for each of my loans but I can't imagine this is the right way to do it. I would have expected that you would setup the loan with the applicable terms and then transfer the amount of the loan proceeds into the asset account. However, I can't seem to figure out how to do this. Help. John In microsoft.public.money, JML wrote: >How does eve...

Chart #3
Hi I have about 5 thousand rows of information with the following: Column A - Date Column B - Time Column C - Level Column D - Velocity Column E - Total I have created a radar chart to read this information but I cannot get the X axis to change - I am trying to change the scale to show me every 12 hours or 24 hours. Basically, the times are very close because the machines are checked every 5 minutes so I have a tick mark every 5 and want to increase that. Is this possible? Thanks in advance Ann (Ireland) ...

CRM 3.0 Beta Known Issues
We are testing we har testing the MBS CRM 3.0 Beta and there is of course some errors. My question is - if you can help me getting an overview over known issues - because we dont want test/look in to things where you (Microsoft) have already found errors. Thanks ... and best regards There is no public list of known issues. There are some issues described in the readme files and some in the newsgroups for crm v3.0. Everything you find should be posted in the betaplace website. It's better to have a bug submitted twice then not at all. Furthermore, please post the questions regard...

cell vlue based on cell back colour
Cell Range A1:A35 are different colours (Red, Green, Blue - depending on their value) I want B1:B35 to show a number, based on the colour of adjoining cells. Is there a simple formula that will allow me to do this? TIA >(*-*)< Esra Hi, > Is there a simple formula that will allow me to do this? There may be. How are the colours derived is it conditional formatting or simply fill? If it's the former Post the CF formula(s) What is the algorithm for deciding what colour a cell will be? -- Mike When competing hypotheses are otherwise equal, adopt the hypo...

Mass Deletion in CRM 3.0
Is there any way to delete more than 250 contacts at once? Thanks in advance, not from CRM but if you are very good in sql server and if you can understand the CRM database very well you can easily do it from SQL server -- vilas "CPW" wrote: > Is there any way to delete more than 250 contacts at once? > > Thanks in advance, As far as I know, the only way to do that is the SDK! Markus (from Germany) Thanks - I did some additional searching and I saw a posting that said you 'can' set the DeletionStateCode = 2 for records in the SQL database and the ...

conditional formatting
Thanks Ms-Exl-Learner on my previous post. Related question. I enter 2 into a1 cell and c1 fills with blue. If I enter 3 into a1 then c1 changes back. How can I keep c1 blue while entering something different than 2 into the a1 cell Place the Cursor in C1 cell and hit Cntrl+Spacebar now the Total C Column will be selected, keep in mind that the active cell should be Z1 (Active cell will have a white background after selection also). Now goto Format>>Conditional Formatting>> For Condition1>>Select 'Formula Is'>> Paste the below formula =$A1=...

Previous Cell Comments Appearing when Mousing over cells in one column
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel I am seeing a previous cell comment when I mouse over another cell in a different column of cells. It only happens in the one column and its the same cell comment. How can this situation be fixed so I will not see this comment? Thanks. You actually can delete comments. Go to help menu and click on search type in comment or commenting and see if you can find deleting comments. > Thanks but this is not the problem. I don't want to delete the comments. For the moment I reverted to a previous version that I had...

IF two different conditions are met #2
How do I enter a formula where two different conditions have to be met? I have specific numbers in column A and names in column B and a numbe in column C. I want the formula to state if column A has a specifie number and column B has a specified name and both conditions are me then return number from column C otherwise return 0. HELP -- Anna ----------------------------------------------------------------------- AnnaV's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=961 View this thread: http://www.excelforum.com/showthread.php?threadid=27065 =if(a1=#,if(b1=...

The match and lookup functions can find literal data but not the same data referenced from a cell
I'm using Excel 2002 SP3 With cell f1 set to the value xyz, the formula =match(f1,'sheetname'!$a$1:$a$500,0) returns #N/A I change this to =match("xyz",'sheetname'!$a$1:$a$500,0) and it returns a sensible index for the data. The lookup function fails in the same way. Is this a bug (e.g. interpreting f1 in 'sheetname' instead of the current worksheet), or is there some setting that needs to be tweaked for this to work (e.g. format assigned to f1)? Thanks in advance Jeff My first guess is that the value in F1 isn't really just &qu...

Publisher cannot find my printer #3
Publisher cannot find my printer, although have installed both a physical printer and a couple of image printers. Does anyone have the solution to this problem. The other programs in my office package does recognize my printers ... all of them. Regards - Wallentin Did you try updating your printer drivers? -- JoAnn Paules Microsoft MVP - Publisher How to ask a question http://support.microsoft.com/kb/555375 "Wallentin" <Wallentin@discussions.microsoft.com> wrote in message news:54050C21-E7CA-4D44-A6F0-722D048385BA@microsoft.com... > Publisher cannot find my pr...

why cant i change font size when i use conditional formatting?
When I use conditional formatting in Excel, the font size is grayed out and the options for the border are limited to 5, none of which are the ones I need, is there a way to make more formatting options available? Hi When you are in the process of applying conditional formatting, you are looking at the regular "Format Cells" dialog box which is not a dialog box specifically for conditional formatting. Conditional formatting cannot change the row height or column width of a cell - hence the font names and font sizes are greyed out. Presumably heavy borders change the row height o...

How do I retain blank cell status in an IF statement?
I have a monthly multiline graph to which I add data by day. The graph lines terminate at the first empty cell (day) in the series, which is what I want. However, If I use an IF statement to prime the cells dynamically the graph lines drop to zero. I have not been able to achieve the desired result using "", #NA or NA(). No worksheet cell that contains a formula can be truly blank. You have already rejected the two closest approximations (#N/A and NA() are equivalent). The remaining options are - delete the formulas in cells beyond actual data (you could use an event ...

Conditional Formatting
I have a column of cells that contain dates from 2010 onwards. Is it possible to flag up (by the method of high-lighting a cell) a date for example 2 months before the entered date as so to indicate that it is due to occur in 2 months time? I see that this can be done for "yesterday, today, next week, this month and next month". I was just wondering whether there is a function that allows for this to be done for dates beyond the one month. Try the "format only cells that contain" option and you can use any number you want. If directly entering, you have t...

Shift Cells Up
I have a couple of columns in which I am comparing data and a formula that runs true or false on the right most column. Where it returns false I have inserted a blank line to separate that cell entry, but I then need to shift cells up or down to run the formula again in the rest of he spreadsheet, however I am not getting the option when I right click to shift cells - can anyone help? Could you put conditional formatting on the first 2 columns so that when column 3 indicates false, the cells in cols 1 and 2 are coloured red.... -- bob777 ---------------------------------------------...

detect and repair #3
after I've used the detect and repair function in outlook i've lost all my stored e-mail, my e-mail accounts definitions and all my contacts. how can i restore those files?? thanks You've posted too little information to unravel what you actually did. Nor can we guess your Outlook version. I'd suggest finding your Outlook data file and configuring your current Outlook profile to use it. We cannot provide specific instructions because you posted no specific information. The file you need is your Personal Folders file (*.pst) It's where all the mail, calendar, contacts etc ...

Exchange Server 2003 #3
I have a distribution list in Exchange 2003 that includes two users and two contacts. If we send to the distribution group the email reaches the users but does not reach the contacts. If we send directly to the contacts themselves they receive email successfully. ...

Printing a diary with the date instead of page number. #3
I'm trying to print something like a diary in such a way that when I open it like a book, it shows the same date on both the left- and the right side page. On each page I want an empty table (the same one on each page). Now, I want to print this automatically. So on the first sheet side B shows January 1, on the second sheet side A shows January 1 and side B shows January 2, on the third sheet side A shows January 2 and side B shows January 3 etc. etc. untill December 31 What's the best way to do this using Excel or Word? I'm not quite en expert so please keep it simple ;o) Thank&...

Bring all data from a lstbox to a cell
Dear all, I have a listbox in a form, and I need to bring all the data in this listbox in a cell... How can I do it? lstTest Item 1 Item 2 Item 3 So when I click in a button in this form, the cell (1,1) needs to have all the data: Item 1, Item 2, Item3 I input this code in the button, but it brings only the first item in the lstTest... Cells(1, 1).Value =3D lstTest.List Thanks in advance!!! Andr=E9. Andre, I am assuming your listbox is on a userform. You either need to write the data to the same number of cells as items: Cells(1, 1).Resize(lstTest.ListCount).Value = ListBox1.L...

Conditional Formula to indicate Formula in cell
I've got a formula in a column, but in certain circumstances I may = overwrite this formula with a number (too rare a special case to bother = = covering) What I'd like though is to use Conditional Formatting to alter cell to = Bold or something to show that this is *a manual override* Can't see how you can check for formula present ? Any ideas ? -- = Steve (3) You need a UDF and use that Function IsFormula(rng As Range) IsFormula = rng.HasFormula End Function and use that in the CF. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing...

Why do my formulas sometimes use "Extract" instead of the actual Cell Reference?
This happens to me time to time and I have no idea why. It's really starting to tick me off!! of example I am trying to do a SUMIF. I Type =SUMIF( I click Columns A and B... Formula now shows =SUMIF(A:B, I then Click my criteria which is in C1... the formula does this =SUMIF(A:B,Extract... Why Extract? Why not C1? I have to type in C1 manually if I want to fill the formula down... I have googled it every way I can think of and found nothing. Any help is really appreciated! Thanks My guess is that the C1 cell is named Extract. HTH. Best wishes Harald "Matt.Russett"...

alternating cell colour
I have a spreadsheet where column A has a set of different values. I would like to alternate the cell colour for changing values. For e.g 210 - Cell Colour Green 210 - Cell Colour Green 210 - Cell Colour Green 215 - Cell Colour Blue 215 - Cell Colour Blue 220 - Cell Colour Green 221 - Cell Colour Blue e.t.c I would be grateful if you could provide me a macro that would do just this. -- Message posted via http://www.officekb.com Sub Test() Dim iLastRow As Long Dim i As Long Dim ci As Long ci = 10 iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("A1").I...

VBA conditional formatting
Hi there, I have read up about conditional formatting and understand the *concept* clearly (I think). However, is it possible to display conditional formatting of a subform in datasheet view on a form? If so, can someone please help me with the VBA coding of this, as I need to have more than 3 (or 4 if default is used) colours? Thank you! W -- The Psyber Fox http://www.psyberconsulting.co.za If you use VBA code to set the properties of the controls (ForColor etc), it will affect all rows of the datasheet, so no: that's not possible. You can use that approach in a ...