Conditional Formatting #28

Hi everybody

I use Excel 2002 sp2
Is there a means to have more than 3 conditions for 
confitional formatting ? the dialog box only features 3.

many thanks and best regards

Jean Luc

0
anonymous (74722)
1/22/2004 9:53:27 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
155 Views

Similar Articles

[PageSpeed] 27

J.E. McGimpsey shows a way to get up to 6 different formats using conditional
formatting and a custom number format.

You can't add more conditions, but you may be able to use a worksheet event that
formats the cell the way you want.

Jean Luc wrote:
> 
> Hi everybody
> 
> I use Excel 2002 sp2
> Is there a means to have more than 3 conditions for
> confitional formatting ? the dialog box only features 3.
> 
> many thanks and best regards
> 
> Jean Luc

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/23/2004 12:11:30 AM
Dave
Thank you for your reply, but :
What is a worksheet event ? (Not found in help)
Best regards
Jean Luc

>-----Original Message-----
>J.E. McGimpsey shows a way to get up to 6 different 
formats using conditional
>formatting and a custom number format.
>
>You can't add more conditions, but you may be able to use 
a worksheet event that
>formats the cell the way you want.
>
>Jean Luc wrote:
>> 
>> Hi everybody
>> 
>> I use Excel 2002 sp2
>> Is there a means to have more than 3 conditions for
>> confitional formatting ? the dialog box only features 3.
>> 
>> many thanks and best regards
>> 
>> Jean Luc
>
>-- 
>
>Dave Peterson
>ec35720@msn.com
>.
>
0
anonymous (74722)
1/23/2004 1:37:54 PM
Worksheet events are macros that are run when something changes in the
worksheet.  You don't run them via Tools|macro|Macros... or by clicking a
button.

They sit in the background waiting for you to do something the developer
expected--say put a numeric value in A1.  Then when you do, it notices the
change and does something to help.

Look in VBA's help--not excel's help, and you'll find plenty of stuff.

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

and for notes about events, you can visit David's page:
http://www.mvps.org/dmcritchie/excel/event.htm

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

A typical example is to provide more formatting than Format|conditional
formatting will allow.

Try this against a test worksheet.
Right click on the worksheet tab
select view code
paste this in the code window (usually the right hand side)

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, me.Range("A:A")) Is Nothing Then Exit Sub
    
    With Target
        Select Case LCase(.Value)
            Case Is = 1: .Interior.ColorIndex = 5
            Case Is = 2: .Interior.ColorIndex = 6
            Case Is = 3: .Interior.ColorIndex = 7
            Case Is = 4: .Interior.ColorIndex = 8
            Case Is = 5: .Interior.ColorIndex = 9
            Case Is = 6: .Interior.ColorIndex = 10
            Case Else
                .Interior.ColorIndex = xlNone
        End Select
    End With
    
End Sub

Now get back to excel and type some numbers (1-6) in column A of that worksheet.

(If you've enabled macros, you'll see some changes the fill color.)



Jean Luc wrote:
> 
> Dave
> Thank you for your reply, but :
> What is a worksheet event ? (Not found in help)
> Best regards
> Jean Luc
> 
> >-----Original Message-----
> >J.E. McGimpsey shows a way to get up to 6 different
> formats using conditional
> >formatting and a custom number format.
> >
> >You can't add more conditions, but you may be able to use
> a worksheet event that
> >formats the cell the way you want.
> >
> >Jean Luc wrote:
> >>
> >> Hi everybody
> >>
> >> I use Excel 2002 sp2
> >> Is there a means to have more than 3 conditions for
> >> confitional formatting ? the dialog box only features 3.
> >>
> >> many thanks and best regards
> >>
> >> Jean Luc
> >
> >--
> >
> >Dave Peterson
> >ec35720@msn.com
> >.
> >

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/24/2004 12:40:26 AM
Reply:

Similar Artilces:

Importing an OpenOffice file in .ods ext (Excell format)
I am new to Excell and would like to import an OpenOffice .ods file in Excell. Can anyone tell me how to do this. Do you still have access to OpenOffice? If yes then open it there and save as XLS and then open in Excel... Or open with googledocs and then save as XLS You can try out tools at http://sourceforge.net/project/showfiles.php?group_id=169337 "jejpa" wrote: > I am new to Excell and would like to import an OpenOffice .ods file in > Excell. Can anyone tell me how to do this. "jejpa" <jejbutler@yahoo.com(donotspam)> wrote in message news:F9...

Count format changes
I have a table with a list of personal names in a column, most of which are entered in black text whilst a minority are entered in red text. I would like to know what steps I have to undertake to enter a formula that counts the number of red entries within the column. Any help would be much appreciated. There is a solution at http://xldynamic.com/source/xld.SUMPRODUCT.html -- HTH RP (remove nothere from the email address if mailing direct) "Dr. Juzzy" <Dr. Juzzy@discussions.microsoft.com> wrote in message news:F0B54917-DE79-4EEE-AECF-608EDE87D99A@microsoft.com... &...

How can I keep a cells format, general, from changing after enter.
When entering data into a cell, particularly a four digit number followed by a comma, and three digit dollar figure with a decimal point and no cents, the numerals change format into a single number with commas. Can you give an example: 1234, is entered as text for me or 123.00 reverts to 123 (excel sees the value of the cell and 123=123.00) But if I type: $1,234.00 excel will see the value as 1234 (in the formula bar) and keep the formatting for you, too. John Z. wrote: > > When entering data into a cell, particularly a four digit number followed by > a comma, and three di...

Avoid user having to enter 00 hours when using [mm]:ss format
I am formatting a game sheet for sports events. Time should be entered as mm:ss which I have "custom" fomatted as [mm]:ss. However I cannot find a way to force Excel to read an entry such as 07:28 as 7 minutes and 28 seconds. It always assumes the user is entering hours and minutes. Thanks for any help SBB 60 seconds = 1 minute 60 minutes = 1 hour They both the same "numeric" value. Since you're locking the minutes to prevent the roll-over to hours, How about *USING* hours and minutes, and lock the hours to prevent the roll-over to days? [hh]:mm You'll get...

How to validate time format from a text field?
The goal is to normalize data before converting text fields to datetime fields. There are start times and end times stored in text fields of a table. The problem is that any criteria used in a query causes errors when any function causes text to be converted to a datetime type, and the text field does not contain a valid date or time. I'm familiar with some of the validation functions such as isNull(), but I can't find anything like isTime(). How can I test existing data for valid time format? IsDate works on time as well. -- Doug Steele, Microsoft Acce...

Conditional Formating based on another cell
I have 2 columns of concern DATE and COST. I would like the COST cell to be shaded if the DATE cell is less than a specificed date. Then if it is possible to calculate the COST field but only the ones highlighted. Help is greatly appreciated!! Thanks, Stacey For the conditional format, use the following for the condition of the cost column: =$A$2<TODAY() and so on, where cell A2 is the corresponding date column. Select the appropriate shade on the pattern tab. As far as calculating the cost column if it is highlighted, my only thoughts are to write a macro that you can run that c...

Formating Dates for production schedule
I have a production schedule within an excel document. I have a start date and a release date. Between these dates I have different dates that represent deliverys, approvals and review dates. These dates are sometimes changed due to weather, client issues, etc. Everytime I have to change a date, I have to look at a calendar and make the changes in each cell after the first change. Is there a way to format all the cells after the first start date, so if I change one date, all dates after that will change the same amount of days, leaving out weekends and national holidays. Take into...

$ format within if statement
I am using Microsoft Excel 2000 for a financial spreadsheet. I want to compare two different financial computations and see which is higher for each row. My "if" statement looks like this: =IF(G8>H8,"Category A is higher by $"&(G8-H8),"Category B is higher with $"&(H8-G8)) The problem is, sometimes the dollar amounts are showing with three decimal places, and I only want to show two for dollars. For example, it may say: "Category A is higher with $201.098" How do I get it to show only two decimal places? Thank you in advance for your h...

Formatting a Row Question
Is it possible to conditionally format an entire row based on input into one cell in that row, or am I "rowing" upstream? All help on this appreciated, Bryan Yes, perhaps a simple example to illustrate .. Select row1 (click on the row header) Click Format >Conditional Formatting Under Condition 1, make the settings: Formula Is | =$B1=1 Click Format button > Patterns tab > gray > OK Click OK at the main dialog Now key in a "1" into B1, press Enter Row1 will be coloured gray Clear B1, the colour disappears (Note that the $ sign in the formula is import...

change format for time value
in column a i have duration time listed for minutes and seconds as m'ss". In column b i would like to show the same information as a but i would like to format the value as the total seconds. Ex if a1 = 1'30" then b1=90. What is the best way to do this? One way: B1: =A1 Format B1 as Format/Cells/Number/Custom [s] In article <6A3363E0-0215-439C-92A0-D714D0A63772@microsoft.com>, Qaspec <Qaspec@discussions.microsoft.com> wrote: > in column a i have duration time listed for minutes and seconds as m'ss". In > column b i would like to s...

How to find out the number of formats in excel sheet
How to find out the number of formats in excel sheet. ...

Electronic number formatting
Anybody out there know if it's possible to format numbers to 2 significant figures and using symbol/scientific notation, ie k for thousand m for milli etc. Hope this is a challenge for somebody ;-) ...

format ("07", "MMM")
hello, I am trying to show the month, i.e., "Jul" in a report based on a field with a value of "07" using =format ("07", "MMM") in the text box, it shows Jan, not July. what is wrong? thanks! Try this: =MonthName(7) -- Ken Snell <MS ACCESS MVP> "sbcaco" <sbcaco@discussions.microsoft.com> wrote in message news:739F2889-FB88-48FE-818D-22F319783829@microsoft.com... > hello, > > I am trying to show the month, i.e., "Jul" in a report based on a field > with > a value of "07" ...

screen capture 05-28-07
I have a A\\V@I monitor/DVD/multi system TV that has a connection for my DVD player, ps2, laptop monitor and power. how do i connect my monitor so i can take a screen picture of a video game ??? Can you explane the connection with Access? "Jake" <jake616@yahoo.com> schreef in bericht news:24192C73-E73B-4466-9A87-DB5BC9C091DC@microsoft.com... >I have a A\\V@I monitor/DVD/multi system TV that has a connection for my > DVD player, ps2, laptop monitor and power. how do i connect my monitor so > i > can take a screen picture of a video game ??? > On Mon, 2...

Conditional count 01-25-10
I need to count unique occurances of names in column a which satisfy conditions in two other columns. For instance: A B C Smith faculty annual fund Smith faculty annual fund Jones faculty annual fund Smith faculty new gift Jones faculty new gift The number of unique occurances of Smith in column A where column B=faculty and column C=annual fund. The answer of course is 2, but how do I write a formula ...

convert seconds to timecode format
Hi there, In C1 through to C800 i have values which represent seconds. (time) HOw can i convert that into 00:00:00:000 hours: mins: secs: fraction of secs so 3622.411 (secs) would equal 01:00:22:411 (3600 secs in an hour) thank you for any help or suggestions. John Try this. "G7"=3622.411 =INT(G7/3600) & ":" & INT(MOD(G7,3600)/60) & ":" &INT(MOD(G7,36))..... "JP" > Hi there, > > In C1 through to C800 i have values which represent seconds. (time) > > HOw can i convert that into > 00:00:00:000 &g...

Format conversion
Is there a means by which I can use .pub files in PictureIt! 2001? Nope. And only the Publisher version that used to create the file, or later version can open the Publisher file. -- Don Vancouver, USA, a great city in one of the 45+ countries in America! "Steven Robinson" <sbf11258@netzero.net> wrote in message news:023401c36da1$3846c470$a601280a@phx.gbl... > Is there a means by which I can use .pub files in > PictureIt! 2001? ...

What database format is used in Money
MSISAM is used for versions newer than, IIRC, M99 or maybe M2K. "Jose Luis" <joseluisbr@hotmail.com> wrote in message news:%23JV7FvozEHA.1404@TK2MSFTNGP11.phx.gbl... > > I thought it was a Jet Database or is MSISAM a form of Jet? "Dick Watson" <littlegreengecko@mind-enufalready-spring.com> wrote in message news:uRwmwxozEHA.2200@TK2MSFTNGP09.phx.gbl... > MSISAM is used for versions newer than, IIRC, M99 or maybe M2K. > > "Jose Luis" <joseluisbr@hotmail.com> wrote in message > news:%23JV7FvozEHA.1404@TK2MSFTNGP11.phx....

format in text boxes
In a sql server report service 2005 report, I need to format a few paragraphs before I displayed a chart. Thus, can you tell me is there a way to format messages in paragraphs foramt like you can in word? If this is not an option, do I need to use special carriage control and paragraph indicator characters like '\C'> Thanks! Hi Can you format by T-SQL? "midnight" <midnight@discussions.microsoft.com> wrote in message news:BCFF67CE-FC98-4E73-B814-2E485D2F0548@microsoft.com... > In a sql server report service 2005 report, I need to format a few...

Alternating Row Formats When Cell Value Changes
I have a list of schools with the school names in column B and the school's district number in column C. I'd like to alternate the row shading each time the district number changes. Is there a formula that can be used for conditional formatting, VB code or some other method to do this? Thanks for any help or advice, Andrew My Excel add-in "Shade Data Rows" does that. It shades groups of like valued rows or will shade by every nth row. Choice of shade colors and an option to skip hidden rows. Comes with a one page Word.doc install/use file. It is free upon direct req...

Formatting dates on x axis of chart...!
Win XP HE Excel 2002 Hi, I have a chart with a simple y and x axis. The x axis represents dates. In sheet1 of the spreadsheet being used, the dates are in column A and in each cell--for the moment is a formula: = sheet2, A2, A3, etc. (where the dates are in this format: 12/20/2004.) The result is (obviously) the same number and format 12/20/2004 in sheet1. When I chart, for the life of me I cannot get rid of this format in the x axis labels--I am usually able to define the label format in tha chart itself but, nothing I do lets me change this format. Even when unchecking the "link to sou...

How to change line chart color based on condition like + and -.
I have a line chart that is somtimes negative and somtimes positive. I would like the line when negative to be red and when positive to be black or blue or green. Is there a conditional format procedure for line charts? Hi, This may help http://www.andypope.info/charts/conditionalline.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "egii" <egii@discussions.microsoft.com> wrote in message news:405150D2-D170-4CC1-9F38-1A585172B293@microsoft.com... >I have a line chart that is somtimes negative and somtimes positive. I >would > li...

Help on Help. Help on Format.
I'm converting myself from Paradox to Access and I'm totally frustrated by Microsoft's help system. (non-help system?) Is there a reference source built-in to Access that can be used to look up functions such as "Format"? I know it's possible to use Format to format strings in addition to dates and numbers, but the only help I've found is mostly about formatting dates. In this case I want my query to end up with a text string that is exactly ten characters long. I want to pad the front end with spaces. I want to build a query that returns a text field t...

Include pre-formatted files/pdf's in report
When sending out customer orders I today include an invoice and a packing list printed from MS-Access. However, I would like to add various information to the customer, such as technical information, promotions etc. These extra informations would be defined on each order, so what I am after is how to include these text fragments in the report print-out. Should this be done as images (jpegs/tiff's) or is it possible to use pdf documents? If the customer order for example contains a reference to the text fragments P017, TD061, B741, could I then get these three files printed out li...

Date formatting on a Pivot Chart's Data Table
I have a Pivot Chart that has a text field for series, a date field for category and a number field for data items. In the charting options, I've hidden the legend and shown the data table. When the data table appears in the chart, it shows the dates in the format: "M-d-yyyy hh:mm" I'd like it to show in the format: "MMM-yy" I can't find any options inside the Pivot Chart context menus or toolbar for applying date formatting on the Data Table. I've tried formatting cells in the Pivot Table and it applies them there, but still shows in the format listed ...