.FormatConditions.Count with multiple cells

Perhaps this is a Bob Phillips specialty. My testing has indicated
that this returns one, zero, or negative one:
 1 All in range have condition(s).
 0 None in range have condition(s).
-1 Some but not all in range have condition(s).
Is that reliable? I'm aiming to check .UsedRange if any exist.

There are tons of web examples with "FormatConditions.Count > 0" but
they better be referring to a single cell! It seems nobody uses it
with multiple cells.

----------------------------------------------
Google's perfect record of utter brilliance ends! groups.google.com
shows Google posters' email addresses for anyone in the world to see.
NOT KIDDING. Someone in Goggle is utterly clueless and numbn*tted! Did
they hire someone from M$??

0
8/17/2007 4:00:47 PM
excel 39879 articles. 2 followers. Follow

4 Replies
435 Views

Similar Articles

[PageSpeed] 3

You'll also get -1 if all cells in the range have conditions, but they 
are different conditions.

If all cells in a range have the SAME two conditions, you'll get 2. 
Likewise with 3.

To check the UsedRange, any non-zero result indicates that SOME 
conditions exist.

In article <1187366447.864613.3250@22g2000hsm.googlegroups.com>,
 zofficedepot@hotmail.com wrote:

> Perhaps this is a Bob Phillips specialty. My testing has indicated
> that this returns one, zero, or negative one:
>  1 All in range have condition(s).
>  0 None in range have condition(s).
> -1 Some but not all in range have condition(s).
> Is that reliable? I'm aiming to check .UsedRange if any exist.
> 
> There are tons of web examples with "FormatConditions.Count > 0" but
> they better be referring to a single cell! It seems nobody uses it
> with multiple cells.
> 
> ----------------------------------------------
> Google's perfect record of utter brilliance ends! groups.google.com
> shows Google posters' email addresses for anyone in the world to see.
> NOT KIDDING. Someone in Goggle is utterly clueless and numbn*tted! Did
> they hire someone from M$??
0
jemcgimpsey (6723)
8/17/2007 5:42:57 PM
Well done. Awesome - you really drilled in there! While I know that
you and some of the MVP legends give terrific and accurate info here,
I wonder if there is an "official" explanation in case anyone asks?

Thanks. Thanks a million!
----------------------------------------------
Google's perfect record of utter brilliance ends! groups.google.com
shows Google posters' ACTUAL addresses for anyone in the world to see.
NOT KIDDING. Someone in Goggle is utterly clueless and numbn*tted! Did
they hire someone from M$??

0
8/17/2007 6:08:01 PM
On Aug 17, 12:42 pm, JE McGimpsey <jemcgimp...@mvps.org> wrote:
> If all cells in a range have the SAME two conditions, you'll get 2.
> Likewise with 3.

Relax, I wasn't trying to trip you up on the last post, or of course
this one, but...I copied a cell to an adjacent one, selected the pair
and named as foo, and
?range("foo").FormatConditions.Count
 1
shows in the immediate window.  It seems we're describing two
different situations. Could you give an example of the case that
returns "2"? Mine has single condition in both: Cell value, not equal,
="ok" in each.

Thanks again.
----------------------------------------------
Google's perfect record of utter brilliance ends! groups.google.com
shows Google posters' email addresses for anyone in the world to see.
NOT KIDDING. Someone in Goggle is utterly clueless and numbn*tted! Did
they hire someone from M$??


0
8/17/2007 6:16:23 PM
> Could you give an example of the case that

Doooooh - never mind.  You were clear, and I didn't read it carefully,
but I got it now. You were referring to multiple conditions in each
cell.  Sorry!
----------------------------------------------
Google's perfect record of utter brilliance ends! groups.google.com
shows Google posters' email addresses for anyone in the world to see.
NOT KIDDING. Someone in Goggle is utterly clueless and numbn*tted! Did
they hire someone from M$??


0
8/17/2007 6:22:58 PM
Reply:

Similar Artilces:

Concatenate multiple rows and columns into 1 cell
I have a spreadsheet using 3 columns and n number of rows worth of data. I am trying to create one long string of this data by concatenating in the following order: A1,B1,C1,A2,B2,C2,A3,B3,C3, etc. The only way I know how to do a large range is to individually click on each cell and that will take a very long time. Is there an easy way to concatenate a range such as (A1:C500)? Thank you. You are aware that a cell can take in limited number of characters. Any more than that number, the result will be truncated please. "mj44" <mj44@discussions.microsoft.com> wrote in ...

finding the password to unlock a protected cell/chart
I'm trying answer a survey in an excel sheet and everytime I try to type in an answer a message comes up saying I need to remove protection.When I head up to tools and follow the prompts,I need a password to complete the procedure . Can anyone tell me what that password is ? Thanks P Hi, Have you contacted the original author of the survey? Cheers Andy Pizza wrote: > I'm trying answer a survey in an excel sheet and everytime I try to type in > an answer a message comes up saying I need to remove protection.When I head > up to tools and follow the prompts,I need a p...

Excel 2007
In Excel 2003, one could select a cell, highlight a part of the contained text string and then right-clidk and format text. I could get underlining, bold, font, color, etc. For some reason, with Excel 2007, I can do the same, but when I hit the enter button, the formatting disappears. It reappears if I select the cell and press F2. Is there some setting that I have missed? ...

Excel Cell Protection not Working
Hi All, I am experiencing a problem with Excel cell locking / sheet protection. When I format a range of cells to be locked and then protect the sheet. Only the very first cell selected out of the wider selection becomes read-only. If I unlock this cell then select all the other cells then protect, none of them become read-only. Therefore the protected cell is not moving but instead becomes just one cell on the sheet every time if it is locked. Any ideas? Regards, Sean Usual procedure is.................. CTRL + a to select all cells. Format>Cells>Protection.........

Multiple MX records
Hello, We currently have 2 Exchange 2003 servers in 2 different geographic locations. Each server has its own storage group. We have VPN and have one MX record. Server A allows local users to send and receive email (internal and external) and it also forwards incoming mails from the Internet to server B for users on the other site. Server A serves as an gateway server for server B. Users on server B can send email out to the Internet and to local users in both offices, but they cannot receive Internet mail directly. My question is can we have 2 ingress ports for incoming mail (allowing ser...

Multiple copies of emails
I posted this problem first in the SBS group, but haven't got any responses. Apologies if exchange issues on SBS are off topic here, but I'm getting desperate <sheepish grin> I have an SBS 2003 that keeps delivering multiple copies of emails to users. It only happens if the email has people listed in the cc address, then each user gets 1 copy for every intended recipient, eg if an incoming mail is addressed to Tom, with Dick and Harry in the CC address, then Tom Dick and Harry each get 3 copies of the mail. Email is recieved via pop3, but the problem affects internal mail as we...

Excel 2003
My spreadsheet has auto filter set and has therefore created lists at the top of each column within my specified range of cells. I would like to retain the first 3 lists but delete the rest. Is this possible? Cheers. Deselect autofilter, then select the 3 consecutive columns which you wish to cover with the filter, then reselect autofilter. -- David Biddulph "Champ" <Champ@discussions.microsoft.com> wrote in message news:F942A84F-1EE0-42E7-97A3-E75FD8845905@microsoft.com... > My spreadsheet has auto filter set and has therefore created lists at the >...

Space within cells
How do you create a space between sentences within one cell? Use Alt + <Enter> to add a carriage return to your cell. -- HTH... Jim Thomlinson "J" wrote: > How do you create a space between sentences within one cell? Hit the space bar? If you mean you want to force the next sentence onto a new line within that cell, hit and hold the alt key when you hit Enter. If you see a little box where that alt-enter is, then you have to change the wrap text property. Rightclick on that cell Format Cells|Alignment tab|check "Wrap text" J w...

Item Count Code
I made an html page to display the company name and the item count at the top of the RMS pos screen for a liquor store client. Thought someone else could use it. -Jerry <html> <head> <meta http-equiv="Content-Language" content="en-us"> <link rel="stylesheet" type="text/css" href="style.css"> <script type="text/javascript" language="javascript" src="global.js"></script> <title>Taskpad</title> <OBJECT classid="clsid:44C4C3AC-D0F1-11D2-919D-006...

Multiple Ranges for a Chart
I am trying to use ranges from several pages in one chart. When I set the source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 I get the following error: Reference is not valid HELP, what am I doing wrong? Are you trying to use ranges from multiple sheets for the same X or Y series? I've never seen that done. You can use X axis ranges from one sheet and Y axis ranges from another. "Vic" wrote: > I am trying to use ranges from several pages in one chart. When I set the > source data to: =Sheet1!$A$2:$O$2+Sheet2!$A$3:$K$3 > > I get the following error:...

Counting Distinct Values
Hi, Hope you all had a good Christmas. Is there a way to count the number of distinct values that appear in a column either by a formula or macro? ie. in this list it would return the value 4 as orange appears twice so should only be counted once. apple orange pear orange grape Thanks in advance, GW -- giantwolf ------------------------------------------------------------------------ giantwolf's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24718 View this thread: http://www.excelforum.com/showthread.php?threadid=496644 This should work for you. =SU...

selecting cell problem
when i open excel and click once on a single cell, the cursor starts to select everything when i move the cursor as if im holding down the left click button. anyone know how to fix. Hi check if hitting F8 helps -- Regards Frank Kabel Frankfurt, Germany jason wrote: > when i open excel and click once on a single cell, the > cursor starts to select everything when i move the cursor > as if im holding down the left click button. anyone know > how to fix. i see what your saying about F8 but thats not it. the cells are selected as you move your cursor not by clicking one cel...

multiple one to many
I have a database with multiple tables that are populated with a form with subforms. Basically I have Table A with a record ID field that has a one to many relationship with two other tables (Table B and Table C). I am trying to run a query (for a report) that will show the record ID (and other fields) from Table A with each of the many corresponding fields from the other two tables. But I don't want the info from Table A showing up repeatedly in my report - just once. How can this be done? TIA Papa J First do the query, and don't worry about 'repeating'...

Multiple Outlook.exe processes
Hi! Whenever I close Outlook 2003 it leaves Outlook.exe running in the background. It will do this each time I open/close the program, creating multiple instances, the only workaround is to end the processes manually in Task Manager. (extremely tedious!) I've tried running scanpst.exe to check for folder corruption - it comes back clean - and I've tried to repair my Office installation, but the multiple process irritation still remains. Does anyone know how to fix this? Thanks in advance. Some common reasons why Outlook will not shut down completely when you click either the...

Reference different lines in the same cell
Hi all, I have an excel sheet were in some cells there are multiple lines created by pressing "Alt + Enter". Is it possible to reference each individual line within these cells separately in a formula? Thanks a lot To a certain extent yes. E.g. if A1 contains one Alt+Enter then =LEFT(A1,SEARCH(CHAR(10),A1)-1) returns the content before Alt+Enter, =RIGHT(A1,LEN(A1)-SEARCH(CHAR(10),A1)) after Alt+Enter. -- Regards! Stefi „Panos” ezt írta: > Hi all, > > I have an excel sheet were in some cells there are multiple lines created by > pre...

How can I change the color/type of the borders I put on cells?
When I use the "Cell Border" button on the toolbar to put borders around selected cells, is there a way I can change the color/type of the border lines (for the whole worksheet)? Tools>Options>View and change the colour in the Color dropdown. -- HTH RP (remove nothere from the email address if mailing direct) "Tourkow" <Tourkow@discussions.microsoft.com> wrote in message news:72EDA2CE-6739-434F-A7EA-AF83C7BA9F12@microsoft.com... > When I use the "Cell Border" button on the toolbar to put borders around > selected cells, is there a way I ...

Multiple Email Accounts
I have created 4 email accounts and emails from all accounts are being deposited into the same inbox. Outlook express allowed me to manage these through identities so that each email account had it's own inbox, drafts, sent, etc. Is this possible with Windows Mail? -- Thanks, Ron WinMail doesn't have that option. Take a look ate this tool. WMIDs http://www.oehelp.com/WMIDs/Default.aspx Otherwise, create an "Inbox" for each address and use message rules to divert the messages to their respective folders. -- Bruce Hagen MS-MVP [Mai...

multiple email accounts #9
my question is: I have 3 Email accounts and I would like it when I get an email from a specific account it go to its own Inbox. I have a home business and would like it kept seperate. Outlook 2002, XP user. thanks! Dan railop@msn.com >-----Original Message----- >my question is: I have 3 Email accounts and I would like >it when I get an email from a specific account it go to >its own Inbox. > >I have a home business and would like it kept seperate. > >Outlook 2002, XP user. thanks! Dan > >railop@msn.com >. > I would recomend creating the 3 Inbox fol...

How format cells ?
Hi I want format money for example '12,35 z�' - i want to show this as '12.35 z�' How i may write fiormula to this work ? Fantom ps. Soory but my english is poor Fantom Format>Cells>Number>Custom #,##0.00"z then hit ALT + 0179(on numpad) then add another " Gord Dibben Excel MVP On Tue, 22 Nov 2005 15:30:51 +0100, "Fantom" <szczukot@skasujto.poczta.onet.pl> wrote: >Hi >I want format money >for example '12,35 z�' - i want to show this as '12.35 z�' >How i may write fiormula to this work ? > >Fantom >...

remove bold from cell ref headers
On one of my worksheets it has put the cell headers ie A B C D across and 1 2 3 4 etc down in bold but other worksheets are ok how do I change this to remove bold? Many thanks in advance Angie Format|style|Normal|Modify|Font Tab|remove the Bolding (make it regular) Angiew wrote: > > On one of my worksheets it has put the cell headers ie A B C D across and 1 2 > 3 4 etc down in bold but other worksheets are ok how do I change this to > remove bold? > > Many thanks in advance > > Angie -- Dave Peterson ...

Make Cells Diagonal
How do I make the top "label row" of the columns diagonal (45 degree angle) instead of horizontal. I have seen it done but don't know how to do it. Thank you. ...

How can I search a worksheet for content matching a specified cell's content
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel I want to search a worksheet for a value that matches the value in a specific cell then returns the contents of a cell related to the matched cell. Something like: <br> If B is the cell whose contents (a number) is equal to the contents of cell A, return the contents of C(B), where C(B) specifies a cell relative to B. You want the VLOOKUP function. Read its entry in the help very carefully: it's tricky. Make sure you set range_lookup to FALSE, so you know when you did NOT find something. Cheer...

Multiple views Single Document MDI
Hi Folks, I have a situation where I need 4 views in my application. All the 4 views should co exist and shall be updated independently/ simultaneously. I searched the forum and all I got was how to create multiple views for a SDI and for MDI creating multiple document templates and make them refer to a single document . I dont know whether creating multiple document templates is a good solution as I read somewhere it is not an optimal solution. Is there any simple/optimal method by which I can go on adding a view for the document? If possible please paste a link to the sample application....

count #10
I have a row of data in which cell 'A' is having item code which is repeating. what i need is to count each items.For eg. A 1 2354 2 2333 4 2338 5 2354 6 2354 7 2338 I want the count for each item in another sheet like 2354 3 2333 1 2338 2 I don't wan to use subtotal. I have got five different item numbers Can someone help me for this... Thanks Toms -- SMILE ------------------------------------------------------------------------ SMILE's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4880 View this thread: http://www.excelforum.com/...

Update multiple worksheets
:confused: What is the simplest method to update several worksheets a once. My report involves several steps where I create multiple pivo tables and from there I update 2 different workbooks from the pivo tables I've created. I use one set of data to create charts and fro the other data I create a report in Excel that has 2 worksheets that update manually. I currently link the worksheets but I'm concerne that I might accidentally select the wrong cell. I'm sure there is simplier way but I don't where to start -- Lizz45i -----------------------------------------------...