Mutiple Conditional Formatting (more than 4)

Is there a way to have more than the 4 conditional formatting?  I have a 
sheet that I want 12 conditional formatting.  All looking in colum A and 
highlighting each one with a different color. 


0
Phillip
11/4/2004 8:13:38 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
215 Views

Similar Articles

[PageSpeed] 59

Hi Phillip

You need VBA to do this

Here is a example that use the Change event of the worksheet
Right click on a sheet tab and choose view code.
Paste the event in there and press Alt-Q to go back to Excel.

This example will only work in column A

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Dim Number
Number = Target.Value
Select Case Number
Case 1 To 5
Target.Interior.ColorIndex = 3
Case 6, 7, 8
Target.Interior.ColorIndex = 5
Case 9 To 10
Target.Interior.ColorIndex = 8
Case Else
Target.Interior.ColorIndex = 10
End Select
End If
End Sub


Frank Kabel and Bob Phillips are working on a Great Add-in with
30 options.





-- 
Regards Ron de Bruin
http://www.rondebruin.nl


"Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message news:OwnDTrqwEHA.2836@TK2MSFTNGP11.phx.gbl...
> Is there a way to have more than the 4 conditional formatting?  I have a sheet that I want 12 conditional formatting.  All looking 
> in colum A and highlighting each one with a different color.
> 


0
rondebruin (3790)
11/4/2004 9:16:19 PM
Thanks for writing back.  I'm sorry, but I'm very new at this and I don't 
know anything about VB.  I did as you said, but what next?  When I went to 
conditional formatting, I still only see 4.  Was I suppose to change 
something in your codes?  Please help!

Does Frank Kabel or Bob Phillips have an email signup that will notify us 
when they have this add on completed?

Phil

"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message 
news:%23M7NKOrwEHA.1308@TK2MSFTNGP09.phx.gbl...
> Hi Phillip
>
> You need VBA to do this
>
> Here is a example that use the Change event of the worksheet
> Right click on a sheet tab and choose view code.
> Paste the event in there and press Alt-Q to go back to Excel.
>
> This example will only work in column A
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 1 Then
> Dim Number
> Number = Target.Value
> Select Case Number
> Case 1 To 5
> Target.Interior.ColorIndex = 3
> Case 6, 7, 8
> Target.Interior.ColorIndex = 5
> Case 9 To 10
> Target.Interior.ColorIndex = 8
> Case Else
> Target.Interior.ColorIndex = 10
> End Select
> End If
> End Sub
>
>
> Frank Kabel and Bob Phillips are working on a Great Add-in with
> 30 options.
>
>
>
>
>
> -- 
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message 
> news:OwnDTrqwEHA.2836@TK2MSFTNGP11.phx.gbl...
>> Is there a way to have more than the 4 conditional formatting?  I have a 
>> sheet that I want 12 conditional formatting.  All looking in colum A and 
>> highlighting each one with a different color.
>>
>
> 


0
Phillip
11/4/2004 10:43:20 PM
Philip, Ron is saying that you can't have more than 4 conditional formats in 
a cell. It drives me nuts too.

Ron is saying that as an alternative you can use VBA to create an equivalent 
to conditional formatting.

His approach may work for you, but there is certainly a lot of effort to get 
it to do what you want.

I can only suggest that you either learn VBA or decide on another way to 
display your business requirements.

James.

"Phillip Vong" wrote:

> Thanks for writing back.  I'm sorry, but I'm very new at this and I don't 
> know anything about VB.  I did as you said, but what next?  When I went to 
> conditional formatting, I still only see 4.  Was I suppose to change 
> something in your codes?  Please help!
> 
> Does Frank Kabel or Bob Phillips have an email signup that will notify us 
> when they have this add on completed?
> 
> Phil
> 
> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message 
> news:%23M7NKOrwEHA.1308@TK2MSFTNGP09.phx.gbl...
> > Hi Phillip
> >
> > You need VBA to do this
> >
> > Here is a example that use the Change event of the worksheet
> > Right click on a sheet tab and choose view code.
> > Paste the event in there and press Alt-Q to go back to Excel.
> >
> > This example will only work in column A
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column = 1 Then
> > Dim Number
> > Number = Target.Value
> > Select Case Number
> > Case 1 To 5
> > Target.Interior.ColorIndex = 3
> > Case 6, 7, 8
> > Target.Interior.ColorIndex = 5
> > Case 9 To 10
> > Target.Interior.ColorIndex = 8
> > Case Else
> > Target.Interior.ColorIndex = 10
> > End Select
> > End If
> > End Sub
> >
> >
> > Frank Kabel and Bob Phillips are working on a Great Add-in with
> > 30 options.
> >
> >
> >
> >
> >
> > -- 
> > Regards Ron de Bruin
> > http://www.rondebruin.nl
> >
> >
> > "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message 
> > news:OwnDTrqwEHA.2836@TK2MSFTNGP11.phx.gbl...
> >> Is there a way to have more than the 4 conditional formatting?  I have a 
> >> sheet that I want 12 conditional formatting.  All looking in colum A and 
> >> highlighting each one with a different color.
> >>
> >
> > 
> 
> 
> 
0
JamesRS (21)
11/5/2004 1:06:01 AM
Gotcha.  Thanks!  Unfortunately, I do not know VB so I guess I'm screwed. 
That sucks.


"James R-S" <JamesRS@discussions.microsoft.com> wrote in message 
news:A7D2268F-E276-41D1-87A0-7DDC4C4BC062@microsoft.com...
> Philip, Ron is saying that you can't have more than 4 conditional formats 
> in
> a cell. It drives me nuts too.
>
> Ron is saying that as an alternative you can use VBA to create an 
> equivalent
> to conditional formatting.
>
> His approach may work for you, but there is certainly a lot of effort to 
> get
> it to do what you want.
>
> I can only suggest that you either learn VBA or decide on another way to
> display your business requirements.
>
> James.
>
> "Phillip Vong" wrote:
>
>> Thanks for writing back.  I'm sorry, but I'm very new at this and I don't
>> know anything about VB.  I did as you said, but what next?  When I went 
>> to
>> conditional formatting, I still only see 4.  Was I suppose to change
>> something in your codes?  Please help!
>>
>> Does Frank Kabel or Bob Phillips have an email signup that will notify us
>> when they have this add on completed?
>>
>> Phil
>>
>> "Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
>> news:%23M7NKOrwEHA.1308@TK2MSFTNGP09.phx.gbl...
>> > Hi Phillip
>> >
>> > You need VBA to do this
>> >
>> > Here is a example that use the Change event of the worksheet
>> > Right click on a sheet tab and choose view code.
>> > Paste the event in there and press Alt-Q to go back to Excel.
>> >
>> > This example will only work in column A
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> > If Target.Column = 1 Then
>> > Dim Number
>> > Number = Target.Value
>> > Select Case Number
>> > Case 1 To 5
>> > Target.Interior.ColorIndex = 3
>> > Case 6, 7, 8
>> > Target.Interior.ColorIndex = 5
>> > Case 9 To 10
>> > Target.Interior.ColorIndex = 8
>> > Case Else
>> > Target.Interior.ColorIndex = 10
>> > End Select
>> > End If
>> > End Sub
>> >
>> >
>> > Frank Kabel and Bob Phillips are working on a Great Add-in with
>> > 30 options.
>> >
>> >
>> >
>> >
>> >
>> > -- 
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl
>> >
>> >
>> > "Phillip Vong" <phillip_vong*at*yahoo*dot*com> wrote in message
>> > news:OwnDTrqwEHA.2836@TK2MSFTNGP11.phx.gbl...
>> >> Is there a way to have more than the 4 conditional formatting?  I have 
>> >> a
>> >> sheet that I want 12 conditional formatting.  All looking in colum A 
>> >> and
>> >> highlighting each one with a different color.
>> >>
>> >
>> >
>>
>>
>> 


0
Phillip
11/5/2004 1:43:16 PM
Reply:

Similar Artilces:

cannot install Office 2001 Combo update 9,0,4
When I try to launch it on my G4, the Classic Environment is automatically launched. Then I get the following message: "The default location for this install cannot be found.This install will be cancelled." I have no trouble with other installs. Hi It sounds like you may have put your Office 2001 installation in a place other than the default location and the updater can't find it. Is that possible? -Jim Gordon Mac MVP All responses should be made to this newsgroup within the same thread. Thanks. About Microsoft MVPs: http://www.mvps.org/ Search for help with the free Googl...

CELL FORMATTING #9
WHY IS MY CELL FORMAT AT WORK DISPLAYED PROPERLY AS -1,500.OO IS DISPLAYED AS (1,500.00) AND WHEN i DO IT AT HOME Ii CANNOT GET THE CLOSEST I GOT IS -1,500.00 is there a diffence between the formatting cell options between Excel's programs.. Thanx. Hi Bumpa! Excel takes it from your Windows Regional options. Use: Start > Settings > Control Panel > Regional options Change the negative number format You'll find that you now have () options. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classific...

Conditional Formatting Problem #7
Is there a way to make conditonal formatting work when there is a formula in the cell? Conditional foramtting works if there is no formula in the cell. If one sheet is linked to another that doesn't work either. Is there a way to get around this? Under conditonal formatting there is a conditon "formula is." What would you put in there to have the cell format they you would like? There are two different things: a) the cell, containing a cell formula, a value or nothing b) the condition, containing a boolean formula (formula is) - b) can refer to a), but a) referring to a...

outlook 2000 contacts #4
Where is the file that contains the contacts in Outlook 2000? I backed up my hardrive to a DVD and had to format my drive. I want to restore the contacts but don't know how It's under your profile c:\documents and settings\<profile name>\local settings\applications data\microsoft\outlook The file is Outlook.pst Good luck. "Ted" wrote: > Where is the file that contains the contacts in Outlook 2000? I backed up my > hardrive to a DVD and had to format my drive. I want to restore the contacts > but don't know how jdc <jdc@discussions.microso...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

Version info with condition
Hi Everyone, I have a project that needs to be compiled under two different names. I have a preprocessor _X_ for one and _Y_ for the other. The part where I'm having problems with is the Version information. I have two version information (VS_VERSION_INFO) one with _X_ in the condition property and one with _Y_ Well, with either build I don't get any version information in the exe. Any ideas? AliR. Have you defined _X_ or _Y_ in the "Resources" section of the project's properties? (Not just in the C/C++ section) On 15 feb, 18:14, "AliR \(VC++ MVP\)"...

CRM 4.0 records reverting to old state; caused by upgrade, rollup?!?
Hi all, A quick timeline before explaining my issue: - We installed CRM 3.0 a couple of years back. We imported old data from ACT! and used a custom drop down field with the value "Migrated from ACT!" to distinguish. - Users have put in a lot of work cleaning up those records, merging when necessary, assigning to right owners... - At some point we installed rollup 1 for 3.0 successfully. - Last year, mid-December, we upgraded to CRM 4.0 (I believe) successfully. - A month later, mid-January, we installed rollup 1 for CRM 4.0. - A few months later, some users were complaining about d...

Userform Textbox Format Problem
I'm having a problem with the formatting of a textbox on a userform. Basically, I have the text box linked to a cell that shows a percentage. However the text box shows the value in the "Scientific" format. Is there a way that I can make the text box show the value exactly as I have it in my workbook? If not, is there a way to format it so that it appears the way I want it to appear? Any help will be greatly appreciated. Thanks in advance. --- Message posted from http://www.ExcelForum.com/ Maybe you could just drop the linked cell (called the controlsource in a userfor...

Conditional Formatting #4
Hello All How would one go about conditional formatting Sheet 2 A1 if Sheet 1 A1 said TRUE ?? Thanks in advance Ian ...

What graphic format?
Are the graphs that Excel make Metafiles, Bitmaps, or are they Vector graphics? -- Siberian ------------------------------------------------------------------------ Siberian's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25361 View this thread: http://www.excelforum.com/showthread.php?threadid=389050 ...

Can not get Excel macros to save chart format -lost in space!
Help??? I've been searching this forum for 5 days trying to find a problem similar to what I am having without any luck. I have been trying to use Excel macro to record the creation, formating and saving of a simple bar chart against my Excel data range but the macros do not run for the chart. I am able to creat the charts okay but the macro craps out when I run it. I keep getting " Set ActiveChart = ActiveSheet.ChartObjects(Chart1)" type errors as if it's not recognizing the chart that I just made. I am an advanced Excel person BUT not so with VB coding. ...

Formatting toolbar disappeared: Reposted, no answer
This issue still has not been resolved. The thread seems to have dropped, so I am reposting it. Below are the previous answers: Thanks, -- df ------------------------------------------------------------- Can't use Word as editor, still using Office 2000. Waiting for next version. HTML is the default mail format. New message | Right click on toolbar | No "Formatting" toolbar is listed. "Standard" and "Customize" are the only choices. That is the problem. Why does it not appear on the list? Thanks, -- df "Mary" <Mary@discussions.microsoft.com&...

surveys in ms crm 4.0
we want to develop a survey application in ms crm 4.0 . IS there any way to do it ??? If yes then how ?? thanks in advance If this is a "permanent survey" like a customer satisfaction survey sent after each resolved case, then you can create a custom entity and link it to the relevant entity (case in this example). Secondly, create a custom aspx application which collects this survey information and uses the CRM web services to create a record for that. "nafees ahmed" wrote: > we want to develop a survey application in ms crm 4.0 . IS there any way to > do ...

Silverlight 4
I went to to http://www.microsoft.com/getsilverlight/Get-Started/Install/Default.aspx to download and install Silverlight but it says my OS\Browser is unsupported. I have XP SP3 with Firefox 3.6.3. Is Silverlight no longer supported for XP? I'd hazard a guess that it is the 3.6.3 that is confusing it. http://www.microsoft.com/getsilverlight/Get-Started/Install/Default.aspx Steve999999 wrote: > I went to to > http://www.microsoft.com/getsilverlight/Get-Started/Install/Default.aspx to > download and install Silverlight but it says my OS\Browser is unsupported. I ...

Force English formatting
I have an application used by both English and European clients. This is significant because often when European clients are entering Data into the TextBoxes, they will accidentally enter it in the format that they are used to: 1,000.00 in EU formatting = 1.000,00. This of course screws up my whole application. How should I go about safe-guarding this from happening? More Info: What I'm looking for is not a way to safe-guard entering the info in the TxtBox wrong, but more of a way to convert it if it is sitting in the cell wrong upon Worksheet Open... Some European clients enter d...

CRM 4.0: Deleting system views
In CRM 4.0 I cannot find a way to hide or delete system views (Accounts: No Orders in Last 6 Months). In CRM 3.0, you could work around this by sharing with a "dummy" user, which would hide it from the rest of the users. I cannot find such a work-around in 4.0; has anyone else? Hi Mike, You can read my blog on how to hide it using a Plugin. http://msdynamicscrm-e.blogspot.com/2008/02/hiding-view-in-crm-40-using-plug= -in.html Hope this helps! Darren Liu, Microsoft CRM MVP Crowe http://www.crowecrm.com On Apr 18, 1:38=A0pm, MikePSU <mike.szyme...@gmail.com> wrote: > I...

need some help with: formatting of x2 dates in 1 cell
Hi and thanks to anyone who reads this. I have a worksheet which contains two columns of dates. In a second worksheet i have a column which adds the two dates together as TEXT and ommits dates which are blank which works perfectly, however: I would like to know how i could format each of the 2 dates in the 1 cell to have different font colors? Here is my existing cell formula: =IF('Data'!E2=0,"",(TEXT('Data'!E2,"dd/mm/yy"))&" "&IF('Data'!F2=0,"",TEXT('Data'!F2,"dd/mm/yy"))) I have a feeling its not...

cell format update problem #2
say, c1, c2 is formatted as text. and a1,a2,b1, b2 are formatted as general. a1=1,a2=2, b1=1, b2 =2 and I entered c1 =a1+b1, c1 shows =a1+a2 as it is, not 3. I drag the corner of c1 and copy c1 to c2, c2 is a2+b2, So I have to F2 and enter all the cells, c1, c2. Is there a way other than "F2" and "Enter"? Consider formating c1 and c2 as general. Good Luck. "news.microsoft.com" wrote: > say, > c1, c2 is formatted as text. > and a1,a2,b1, b2 are formatted as general. > > a1=1,a2=2, b1=1, b2 =2 > > and I entered c1 =a1+b1, > c1 shows...

Two different formats for same document
I print scripts onto 8x5 index cards, landscape. I'd like to be able to print a copy of the script, two pages per sheet, onto 8.5x11 paper for editing purposes. The index card page breaks are important to know in the editing. Any ideas? I use MS Word 2008 for Mac. I can't give you explicit directions without more details but I believe you should find your solution in the Print dialog. There should be nothing you have to change in your document itself. In the Print dialog open the Copies & pages list, select Layout. Choose 2 in the 'Pages per Sheet' lis...

Can Excel do this? #4
Greetings, I am new to this forum and have a question about what Excel is capable of. I asked around with friends and co-worker only to discover that I know more about Excel than they do. I created a sport specific score sheet (scores based on time) in which stage times, time penalties, etc. are summed and placed in an �overall time� cell. So far so good (everything works as planned). Now what I wish to do is have, not a number, but a word such as �novice� show up in a �rating� cell if the value in the �overall time� cell falls between two time limits and a different word such as �expert...

Negative Number Formats
Can anyone help? I have Excel 2003 and despite setting=20 all regional options correctly and specifying number=20 formats as (99), (=A399) etc I still can't get Excel to=20 display them in parentheses. I seem to remember from=20 Excel XP that you have to install/uninstall=20 certain "foreign" language options but can't remember=20 what. Please e-mail if you have the key! Thanks I'd double check that windows setting: Windows Start button|settings|control panel|Regional Settings Currency Tab|Negative Number Format (that was the path I used in Win98.) Paul Handley wro...

Date Format Changed when convert to Excel
Dear All, there is a problem that when i convert the text file to excel then date formart for the first 12 dates have been changed with "/" sepreator and other remains with "-" that creates a lot of problem please help to reslove the issue. On Wed, 19 Sep 2007 22:27:06 -0700, Ather <sohail.ather@paktel.com> wrote: >Dear All, > >there is a problem that when i convert the text file to excel then >date formart for the first 12 dates have been changed with "/" >sepreator and other remains with "-" that creates a lot of problem >pl...

Excel 2003 List Formatting
Good Morning. When I enter a new row of record in a List, the values I enter take on the alignment of the headers, which are left indent 1. I would like the values to right indent 2 as I enter the values. How can I do this? Thanks, Ron Instead of entering a blank new row, copy a complete existing row with the correct alignment. Click on the row number above which you want the new row; then hold down Ctrl and press the + key on the numeric keyboard. Then enter the correct data in the pasted row. "Ron_D" <Ron_D@discussions.microsoft.com> wrote in message news:86CE38F9...

[$-409] in Custom Number Formats
Hello All, I've seen [$-409] in some number formats and I was looking through the help files to see if I could find out what it means. I couldn't find anything on it. I've also seen [$-F800]. Does anyone know what these codes are for or where I can find info telling me what they are for and any others that can be used? Thanks for any help anyone can provide, Conan Kelly This was covered in the Daily Dose of Excel blog about a month ago: http://www.dailydoseofexcel.com/archives/2006/02/27/months-of-the-world/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Techn...

Mail merge address to 4-up postcard
I have a post card designed and it is quarter page format, making 4 post cards per page. I now want to mail merge the addresses onto the post cards for printing. My mail merge seems to be working as it brings up the address, but it prints the same address on all 4 post cards per page. How do I get it to give a different address for easch post card? -- Rich D Armstrong Custom Homes Redmond When you merge you can only have one card on your screen. If you are relying on the print preview in Publisher 2002-03 this is a Publisher bug. It will show all 4 cards the same. If you are unsure p...