formula based on format

Is there a way to have a formula that is based on a cell colour or based on 
the way a cell is formated?  I need it to count the number of occurrences 
that this happens.
-- 
Thank you for your time.
Windows NT
Office 97
0
12/16/2004 2:27:01 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
434 Views

Similar Articles

[PageSpeed] 13

Hi
you'll need VBA. See for an example:
http://cpearson.com/excel/colors.htm

"James Kendall" wrote:

> Is there a way to have a formula that is based on a cell colour or based on 
> the way a cell is formated?  I need it to count the number of occurrences 
> that this happens.
> -- 
> Thank you for your time.
> Windows NT
> Office 97
0
frank.kabel (11126)
12/16/2004 2:29:05 PM
This is new to me.  I have copied it into the VB sheet and placed the 
following formula in a cell but it only shows #Name.  What am I doing wrong.  
I did this to both the countbycolor and the return colorindex value functions 
and neither work.



"Frank Kabel" wrote:

> Hi
> you'll need VBA. See for an example:
> http://cpearson.com/excel/colors.htm
> 
> "James Kendall" wrote:
> 
> > Is there a way to have a formula that is based on a cell colour or based on 
> > the way a cell is formated?  I need it to count the number of occurrences 
> > that this happens.
> > -- 
> > Thank you for your time.
> > Windows NT
> > Office 97
0
12/16/2004 3:55:07 PM
Hi
you also have to put the macro code in a module. See:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

-- 
Regards
Frank Kabel
Frankfurt, Germany
"James Kendall" <JamesKendall@discussions.microsoft.com> schrieb im 
Newsbeitrag news:2A2D556B-1556-4817-A9E4-21AD63F4CE8A@microsoft.com...
> This is new to me.  I have copied it into the VB sheet and placed the
> following formula in a cell but it only shows #Name.  What am I doing 
> wrong.
> I did this to both the countbycolor and the return colorindex value 
> functions
> and neither work.
>
>
>
> "Frank Kabel" wrote:
>
>> Hi
>> you'll need VBA. See for an example:
>> http://cpearson.com/excel/colors.htm
>>
>> "James Kendall" wrote:
>>
>> > Is there a way to have a formula that is based on a cell colour or 
>> > based on
>> > the way a cell is formated?  I need it to count the number of 
>> > occurrences
>> > that this happens.
>> > -- 
>> > Thank you for your time.
>> > Windows NT
>> > Office 97 


0
frank.kabel (11126)
12/16/2004 4:07:30 PM
Thanks it works!  And I sure learned a lot today!  Thanks again.
James Kendall

"Frank Kabel" wrote:

> Hi
> you also have to put the macro code in a module. See:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> 
> -- 
> Regards
> Frank Kabel
> Frankfurt, Germany
> "James Kendall" <JamesKendall@discussions.microsoft.com> schrieb im 
> Newsbeitrag news:2A2D556B-1556-4817-A9E4-21AD63F4CE8A@microsoft.com...
> > This is new to me.  I have copied it into the VB sheet and placed the
> > following formula in a cell but it only shows #Name.  What am I doing 
> > wrong.
> > I did this to both the countbycolor and the return colorindex value 
> > functions
> > and neither work.
> >
> >
> >
> > "Frank Kabel" wrote:
> >
> >> Hi
> >> you'll need VBA. See for an example:
> >> http://cpearson.com/excel/colors.htm
> >>
> >> "James Kendall" wrote:
> >>
> >> > Is there a way to have a formula that is based on a cell colour or 
> >> > based on
> >> > the way a cell is formated?  I need it to count the number of 
> >> > occurrences
> >> > that this happens.
> >> > -- 
> >> > Thank you for your time.
> >> > Windows NT
> >> > Office 97 
> 
> 
> 
0
12/16/2004 4:47:08 PM
Reply:

Similar Artilces:

Qn on Restricting a public function overriding in base classes ?
How do we achieve the following : Class Baseclass { public: int funXYZ(); ..... }; Now, How do I modify the definition of the above class to restrict any other Class which derives from Baseclass, from over riding public function funXYZ(); Note that, the function funXYZ() needs to be public. Is it possible ? "satlal" <satish.lalam@gmail.com> skrev i meddelandet news:dg942b$67e$1@news.mch.sbs.de... > How do we achieve the following : > > Class Baseclass > { > public: > int funXYZ(); > ..... > }; > > Now, H...

conditional format formula
I am trying to use a formula in conditional format to turn a cell grey if the date in the column is not yet passed. not colored not colored should be grey a b c 10/20/04 10/21/04 10/22/04 5:00 5:00 5:00 =IF(NOW()<a$2,,) Is my formula. Whether I use < or > does not matter, th...

Script for Query-Based DL's
Pardon me if posted in the wrong place. We are scripting a large amount of OU structures and administration groups with delegation through dsacls. This is something that is repeated many times. One item that is asked to create is a query-based DL. Since it would need to be ran so many times and for consistency I am trying to find if we can have the script create the Q-B DL's. I have found scripts for dsacls and the Q-B DL's but I'm having a hard time finding how to script the creation of the DL's. Does anyone know how this would be done? Thank you, Michael ...

format numbers in hexadecimal
I'd like to display numbers in hexadecimal and still be able t calculate with them. DEC2HEX() and HEX2DEC() are available as functions (I think they're i the Analysis ToolPak) but I'd rather avoid adding these to ever calculation. That is, for example, I don't want to have to change every formula "=A1+A2" to "=dec2hex(hex2dec(A1)+hex2dex(A2))". Any idea how to add "Hex" as another numeric format choice, so tha numbers are displayed directly in hex -- Message posted from http://www.ExcelForum.com stan_shepherd wrote... >I'd like to ...

Conditional formatting with dates formula problem.
Hello. I appreciate help on this topic. I'm very new to excel's conditional formatting capabilities and I need help on the following: I have a worksheet where I am using columns A and B to be fashioned into a type of "reverse" library checkout card; I want to flag when 120 days have passed since an item has been checked out. All cells are blank with the exception of the formatting applied to cells in column A. For example, Condition 1 on cell A1 has the formula: =IF(ISBLANK(B1),(A1-TODAY())<120) **I've also tried =IF(ISBLANK($B1),($A1-TODAY())<120) My objecti...

How to copy rows from Excel without losing the row height format
In Excel 2003. I have a section of a spreadsheet that contains a 25 rows that each have different row height. I want to copy these to a section below, but lose the row height formatting. How do I copy without losing the row height format? Are you copying the entire row? Also, don't forget the format painter and the right-click paste special options. ************ Anne Troy www.OfficeArticles.com "artex boy" <artex boy@discussions.microsoft.com> wrote in message news:4DA6D00F-8957-4569-94C5-EDB2AE6F683D@microsoft.com... > In Excel 2003. I have a section of a sp...

After re-format / re-install of Store OPS, "Check Stores" button is missing.
I had to re-format and re-install Store Operations POS on 3 stations so far and now I am missing the check stores button in the POS when you click on the Lookup button. We have HQ running and it worked fine before. Any insight? Thank you. Matt Matt, Store Ops Administrator > File > Configuration > HQ Client tab > enter the machine name/address and the port number running the HQ Client program -- = <trae78@yahoo.com> wrote in message news:1102806501.391415.197560@z14g2000cwz.googlegroups.com... >I had to re-format and re-install Store Operations POS on 3 station...

Formula to eliminate zeros
I have a column of values that is 200 rows tall. The values are eithe a five digit option number or a zero. (I can change the formula tha generates this column to put anything in place of the zeros of tha helps) I need to transfer those option numbers to Work Order in concise list without 175 to 200 zeros. I need to do this automaticall with a formula when the data is imported so my purchasing dept. doe not have to filter the numerous lists with each job with a drop dow menu. I could have all of the zeros on the bottom if I can get all o the five digit option numbers to appear at the top...

Formula for file location
Hi (I'm a newbie so please be patient) I am using excel to build a database for an online store, i have apro 3000 products and each product needs an image path (eg. D:/m docs/images/123.gif). I need a formula that will automatically insert the path to the image in its cell in the image colomn. To make this easier all the image names are the the part number[.gif so the image for product 123 is D:/my docs/images.123.gif. Can any one offer a formula to do this? Code ------------------- Example of spread sheet -------------------------------------------------- Make Model ...

Make Word 2008 open with Formatting toolbar showing
> This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3284189186_6066939 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: 8bit Is there a way to tell Word 2008 that I want always to have the formatting toolbar showing? What about the formula bar in Excel? I couldn�t seem to get it to stick to the menu bars, much less appear as a default. Thanks! Best, - Bill Leopard 10.5.1 Office 2008 --B_3284189186_6066939 Content-type: text/html; charset="ISO-8859...

Need to format text in header, but value is generated using VBA
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6360C.2372C770 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, I wanted to reference a cell value in my spreadsheet header and found = out how to do that using this function: Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim WS As Worksheet For Each WS In Worksheets ActiveSheet.PageSetup.RightHeader =3D _ Format(Worksheets("Time Period Info").Range("B3").Value) =20 Next WS End Sub However, this leaves my text for ...

array formulas-sumproduct and average
Hello, I need hel. I am using this formula =SUMPRODUCT(--(E2:E289=1),--(AVERAGE (H2:H289))) I don't know what's wrong with it. I need to find the average for column H in relation to 1(under 2 yrs) in Column E. Hope this makes sense. Thanks, Becky Try: =AVERAGE(IF(E2:E289=1,H2:H289)) Array-enter the formula with CTRL+SHIFT+ENTER -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- "Becky" <Becky@discussions.microsoft.com> wrote in message news:38BA387B-4DA3-4274-9CB5-CCC7E976DDAE@microsoft.com... > Hell...

Multi based criteria
I have a report setup that prints a single record. I get to this report by having the record open through a form and a command button to print preview the report. I have various subforms (data only applying to that record) that are also outputted on the report and that's working fine. But now my issue is that I have subreport that has three separate criteria I need to specify. The three controls are BrandName (value), Type (text) and DropNo (value). I have a separate table called OfficeCopies. In this are various people that, depending on the brand, type and drop, are assign...

Data Validation Lists
I have been searching the web all day, and I am not sure how to approach this problem. Here is a basic worksheet: ID Name Serial # 1 Joe ck9033n2389d 2 Joe 349-283fjrjh55 3 Bob c90320j34n5kjdf 4 Joe kgho95injhs 5 Bob 2353gdf745 Step 1: I have a drop down list with only one instance of each of the "Name" variable. Step 2: Upon selecting that name, I would like another drop down box with all the serial numbers associated with that person. Step 3: Then, based on the serial number, display stats in the same row for that i...

when i import a file into a Word it adopts different formatting
When importing a file the document adopts odd formatting. For example - paragraphs suddenly appear in bold - alignment changes etc. It does not adopt the format of the original document. I know that I could use Format Painter to correct this - but the documents are often very long and it is too time consuming to do so. Which styles are applied to the inserted text? Modify those styles in the target document. -- Stefan Blom Microsoft Word MVP (Message posted via NNTP) "Chelbo" <Chelbo@discussions.microsoft.com> wrote in message news:DDEBA41C-0657-4...

URGENT : MS06-29 Breaks EDK Based Fax Gateways
Hello, We are an ISV based in france selling EDK Based Fax Gateways for Exchange 2000/2003 Microsoft Released a patch for Exchange 2003 this week: http://support.microsoft.com/kb/912442/en-us Unfortunately, after installing this patch, the gateway becomes unable to deliver a new message to the Gateway's mailbox. After doing some research, it seems that this is related to the "Send As"/"Receive As" rights-granted to the service account running the gateway if this is not LocalSystem- on the mailbox. Any help appreciated. Yes that would almost certainly be true....

Access autonumber field changes to date format when Excel imports
When importing Access data into Excel, the autonumber field in Access is chenged to date format instead of a general number format. I could run a macro to change the number format but this only happens on some Vista machines, not all. Any suggestions as to why this is happening? ...

copying formula down
Using Excel 2003 Column A is dates. These dates plus 7 are being compared to the current date to determine whether "overdue" gets displayed in Column D. I'm using the following IF statement: If A1+7<today(),"overdue"," " When I copy the formula down, rows that have not yet had data entered into them are all displaying "overdue" because Column A is blank. Do I just need to remember to copy the formula down every time I enter a new row of data, or is there some way I can modify my IF statement to only do the calculation if there is data ...

conditional format a date in Excel
I am trying to use conditional formatting to show when a date is less than 30 days in the future. When I use "cell value" "is less than" "="Now()+30" all the dates change. I am trying to use conditional formatting to show when a date is les than 30 days in the future. When I use "cell value" "is less than" "="Now()+30 all the dates change. lose the quotes in the condition =Now()+30 Unless you mean between today and 30 days in the future in which cas =now() would be the first condition and =now()+30 would be the second Al...

Can I combine a Vlookup with an IF formula?
I'm not sure Excel can do what I want it to do - I have a VLookup formula on my spreadsheet which is returning data, however, I only want it displayed if it returns a specific condition. Is this possible ? -- CMB BT Hi, let' say you want to perform the vlookup if cell A1 is greater than 100, if not will leave the cell blank =if(A1>100,your formula,"") "Claire" wrote: > I'm not sure Excel can do what I want it to do - > > I have a VLookup formula on my spreadsheet which is returning data, however, > I only want it disp...

Excel 2002 Day/Date format
For some reason on one of my computers, Excel won't allow the cell to be formatted to read "Day, Month Date, Year". The option doesn't exist under the Date category of the Format Cells command. Anyone have any ideas? Hi Have you tried using a Custom format? -- Andy. "The Kingfish" <KCKingfish@earthlink.net> wrote in message news:MGjke.4830$X92.1081@newsread2.news.pas.earthlink.net... > For some reason on one of my computers, Excel won't allow the cell to be > formatted to read "Day, Month Date, Year". > > The option doesn&...

Install dates formating using conditional formating?
I have 2 cells (A1 and A2). I would like to have the following take place. 1. If I place a date that is either past or withinthe next 30 days into A2 format the text as red. 2. If I place any date into cell A1, I need A2 to say N/A. I have been able to do #1 using conditional formating but am stuck on #2. Can anyone help? Thanks -- Jerry Eggleston ------------------------------------------------------------------------ Jerry Eggleston's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9898 View this thread: http://www.excelforum.com/showthread.php...

Addition Formula #3
What type of formula do I need to have a column total values in a row if the cells in the rows value is less then a value in a certain cell? Example Row 2 one has dates in it ( 10-1, 10-2, 10-3, & so on) Cell A1 has today’s date in it =TODAY () So I want Cell J3 to date the values in the cells A3 thru I3 of row 3 if the date above them in row 2 is less then Cell A1- today’s date. Thanks Chance ...

Conditional formatting #82
I am trying to do the following: 1. In cell H14, if the cell is blank, turn blue. 2. In cell H14, if the cell has a date less than or equal to another cell, turn green. 3. In Cell H14, if the cell has a date greater than another cell, turn orange. 4. If NA is placed in the cell, revert to no format. I have managed the first three, but cannot get the fourth one to work. What formulas should I be putting in Each condition? What happens when you put NA in the cell? -- Regards, Peo Sjoblom "ChristinaC" <ChristinaC@discussions.microsoft.com> wrote in message news:BD9...

counting cells with conditional formatting applied
I am looking for a way/formula that will count the number of times a cell with conditional formatting has occured. I have a column of cells that is compared to another column of cells and if one condition is met (<), the cell's color is set to Red or if another condition is met (>) the cell's color is set to Green. I am wanting to add a column that gives me the total number of Red cell's in that column. Thanks! Look on Chip's site at www.cpearson.com best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "HalB" <HalB@disc...