Cond. Formatting, Mod(Row) and cell conditions

Is there any way to use the mod row function to shade alternative rows, based 
on whether cells have any value in them.
I want to select a big range (eg A:G) and only shade alternative rows (A:G) 
once data is place in cells.

the mod row function shades regardless if there is data in or not.
0
Utf
5/28/2010 7:22:05 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1940 Views

Similar Articles

[PageSpeed] 0

Select ColA:G and try the below CF formula..Make sure the active cell of your 
selection is in Row1

=AND(MOD(ROW(),2),COUNTA($A1:$G1))

-- 
Jacob (MVP - Excel)


"wynand" wrote:

> Is there any way to use the mod row function to shade alternative rows, based 
> on whether cells have any value in them.
> I want to select a big range (eg A:G) and only shade alternative rows (A:G) 
> once data is place in cells.
> 
> the mod row function shades regardless if there is data in or not.
0
Utf
5/28/2010 9:11:01 AM
Thanks that works great!
I unfortunately have another problem now:
I have data on sheet 1 that is transferred to a preset LIST in sheet 2 with 
VBA.
I also added a sum formula outside of the list which needs to dynamically 
move with the list expansion (Total does not work with the code).

If the rows with data on sheet1 are more than that contained in the preset 
list on sheet 2, either the sum formula dissapears or cond. formatting with 
your formula does not work, as the list is not expanding dynamically or the 
shading stops at the old list settings.

Any ideas?
0
Utf
5/28/2010 9:53:01 AM
Reply:

Similar Artilces:

Running Balance in the same cell...
Ok, I have a spreadsheet that I"m creating to keep track of spending that is coming out of two different accounts. I want to be able to keep a running balance but in the same cell based on a condition. Is this possible. My spreadsheet looks like this. Cell A Cell B Cell C Cell D 1 9000 3000 2 R 100 3 P 50 4 R 400 5 P 100 What I'm trying t...

alternate quoted text formatting?
Is there any way to change Outlook's behaviour vis-a-vi quoted text in replies. It uses vertical bars to indicate quote level, but these just don't show up (in any consistent way) when viewed in any non-Outlook mail client. I'd much prefer Outlook to use ">" for quoted text or at least some method that is not limited to viewing in Microsoft clients. The only way I can find to do this is to use the view all mail as plain text option, but then if I'm forwarded an e-mail from an Outlook user where there is already quoted text in the vertical bars style, these are jus...

optimum method to print rows as columns
looking to convert each spreadsheet row to print as a column per page -- Concord If you've got more than one or two rows to deal with, the optimum is probably the macro below. You'll need to change the names of the two worksheets involved, and the "EmptySheet" referred to may even have to be added to your workbook if all the sheets in it are currently being used for something. To put the code to work: Start by making a copy of your workbook to test things with. Then open that copy and press [Alt]+[F11] and choose Insert-->Module and then copy and paste...

Converting Money 2003 to 2006 file format failure
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C5CD89.F3BC8880 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm trying to evaluate Money 2006 however the update fails when = converting my Money 2003 file.=20 It fails with the "Money cant open this file because it was created = using a different version of Money" - obviously it WAS a different = version, hence the upgrade!!! Do I have to upgrade to Money 2005 to = advance to 2006??? Not a wise move on M$' part if that is the case!!!!. The fail...

Format Column
I have a column with inmate id numbers in it. I imported them from a text based program. I made a custom formatting for the column because all the id numbers begin with zero. IE 00112356 or 01555666 etc... The format I used was 00000000 under custom. The problem now is that I want to import them into access but access doesn't see the zero at the beginning. How can I make it show the literal number including the zeros? I tried adding the '01222555 before the number, but I would have to manually append 2000 records. Is there a faster way? Morph. Hi you could use a helper column...

Sahring microsoft office excel documents in sharepoint in edit mod
> Can users share the same document in 'edit in microsoft office excel' > allowing both the edit the same document, if so where do I set access -- GJEmberson Hi, The answer is yes, however, it is the general consensus amoung the high end users that the feature has too many disadvantages. But if you want to try it: Choose Tools, Share Workbook, and check Allow changes by more than one user... If this helps, please click the Yes button. -- Thanks, Shane Devenshire "Genevieve Emberson" wrote: > > Can users share the same document in 'edit in microsoft...

Change format of the date in Visio?
When I want to use the Timline-function in Visio I get disturbed that it isn't possible to change the format of the date for example for an interval or a milestone to the way i want it. I would like it to be "Friday 16/2" instead of the ways that is possible to chose (not written out the month in letter nor the year). Is it possible to change into this easily? Thanks On Sun, 28 Jan 2007 14:27:01 -0800, Smeeed <Smeeed@discussions.microsoft.com> wrote: >When I want to use the Timline-function in Visio I get disturbed that it >isn't possible to change the for...

Conditional formatting dates
Hi, Help please. I have a column of 'Due Dates' in the mmm-yy format. I want the highlight a 'date' a month before it is due to expire. How is it done? I can get so far with DATEVALUE but then become stucK. Many thanks Hi Lofty, For a date in A1, you could use a formula for the conditional format like: =A1>=DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY())) -- Cheers macropod [Microsoft MVP - Word] "Lofty" <Lofty@discussions.microsoft.com> wrote in message news:848E191C-832F-4C60-924D-32A1AF70A0FA@microsoft.com... > Hi, > Help ...

2 existing spreadsheets show 1st line rather than header row to sort ...
(XL2003) This is odd, I haven't seen this ever before. I was working on spreadsheets yesterday for a mail merge. Two had headers rows. I cleaned up both documents, put print area and then tried to sort. In both cases, what came up in the sort was the first row below the header where one always sees the actual titles of the header row when one selects the Header Row radio button under "My List Has" in the "Sort" box. I fiddled and fiddled a bit and managed to get one of the workbooks to display the sort properly by the header row titles - without figuring out what ...

How to copy cells horizontally to cells out of sequence
I have data in cells that are on Sheet1. This data is in Cells B2, B3, B4, B5 etc (All Horizonally, ie staying in same row). I'm working on Sheet2 and would like to know how to take this data from Sheet1 and copy it to different cells on Sheet2, but have the data fall vertically. I can copy this once and repeat, but I am looking for a formula that will do this for me so I can copy it multiple times. Example: Sheet1, B2, will go to Sheet2, K4. Sheet1 C2, will go to Sheet2, K5 Sheet1 D2, will go to Sheet2, K6 Please help. One way: Enter this in K4 of Sheet2, and copy down as n...

auto clearing cells
i have a worksheet that has questions and answers... prior to filling out answers a job number is typed into a cell .... once done sheet is saved. my question - if a saved sheet is pulled up and job number is changed..... how can i have all previous questions "answers" cleared unless original job number is re-entered ? Yvonne That's what Templates are used for. Save your original workbook.........cleared of course........as a File>Save As>MS Excel Template(*.xlt) Users click on open for this template..........a copy is opened. Users fill that in and save a...

Conditional Formatting
I have a row of cells that calculate the totals for their columns but I force to be blank via IF statements until certain conditions are met. Once these conditions are met, the total appears. I'd like to also highlight these total cells via conditional formatting. My conditional formatting input is simply "If Cell is Greater than 0, format". For some reason all the cells format as though they are all > 0 yet they don't display anything. Are there some internal rules that specifiy when a cell is really 0? Is there another way to do this that won't care what the for...

Tmu220 epson reciept format
i use an epson TMU220 printer, the receipts would print but the paper rolls out about 8 inches extra blank and some of the writing falls off the side what format should i use The TMU200 is not the best printer for RMS. From our experience we only use and recommend TM88IV or the TM88III, either USB or parallel connection work very nice. ===================== Thanks, Shawn Ezhaghi Vitet Consulting Group Ltd. 646-706-7600 xt: 101 "Not just consultants but retailers too." "Andaiye" wrote: > i use an epson TMU220 printer, the receipts would print but the paper r...

Rename Cell Name
hey all, i have change the cell anem from A1 to StartCell. Bu, how can i rename it back to A1 or change it to another name? Thanks in advance Regards Dragon Hi Dragon go into insert / name / define - you can delete the name there and create another if you wish. Cheers JulieD "Dragon" <Dragon@discussions.microsoft.com> wrote in message news:B6A6510E-0233-4B2A-8A0C-F16F73585CBA@microsoft.com... > hey all, i have change the cell anem from A1 to StartCell. Bu, how can i > rename it back to A1 or change it to another name? > Thanks in advance > Regards > Drago...

Formatting cells #5
I have a spreadsheet in which some cells are placing a decimal point in the numbers we enter. I think the cell thinks we are entering dollar amounts. I right click on the cell and choose format cell and on the Number Tab the Category Field is marked General and to the right it says "General format cells have no specifc number format". However in the sample box is shows 283802.07, it is placing a decimal point anyway. Any ideas on how to fix this? I just need to enter the numbers without a decimal point Thanks Anthony Tools/Options/Edit uncheck the Fixed decimal places checkbo...

Cond Formatting
Hiya, I'm in cell G5 which is where I have "S" for Saturday, the next cell is "S" for Sunday etc.... 1) What I would like to do his put a shade of grey in the column starting at G7 to say G120, and then I'd obviously copy it to the remainder of the "S's." 2) I also have other CF in that column, such as if "W" is entered it changes to a green background (but no CF in G5), will one over ride the other, and or will there be a conflict... that is once I learn how to do question one.... :-) Thank you, P.S. I like this newsgroup, it gets t...

Is there a way to hide text in a cell?
I'm trying to format a spreadsheet where some information (passwords) should only be viewed by certain people.... not for the general viewer. Is there a way to hide text in a cell? I have done it in the past but I can't seem to figure this out now. IT For a very casual and inexperienced user you may get away with something like this, but anything you do can be easily un-done by just about anyone who could access these news groups. You could format the text to white so's it wouldn't show in the cell. You can go to Format>Cell>Protection and check "hidden"...

Formatting a DOC file in Outloock
I'm doing a newsletter in Word, and want to be able to send it as the body of an email. When I do, the formatting is shot to heck. Is there any way to LOCK the format or something so that the email goes out looking like the doc file itself? Any help would be appreciated. Ed ...

ripping mps format cd's
I can rip any cd but a cd that is in mp3 format so I can sync to mp3 player.. Can someone please help.. P.S. i have a vista home basic with win media player.. -- Yours Truely, Brian Ripping is for Audio CDs (PCM format). If you have a CD with .mp3 files on it it is a data disc. Just copy the files to your hard drive (wherever you keep your music) and go on from there. "Brian VQ" <BrianVQ@discussions.microsoft.com> wrote in message news:813CD5AE-A9D5-4097-A442-21A3E73BCB3D@microsoft.com... >I can rip any cd but a cd that is in mp3 format so I can sync to mp...

column / row merge sort
I have a spreadsheet that consists of rows of names and columns of data pertaining to that name information. If a name has more than one occurance, that name has more than one row. I want to consolidate all the data for one name to 1 row. Short of doing it all by hand is there a better way? thanks in advance! JD Depends on what you want to do with the data and what the data is. If it's just numeric data, it may be as painless as using a pivottable to summarize it. If the data contains text, then maybe concatenating fields (multiple lines in one cell?) or maybe you want to jus...

Time formatting
I need to be able to right a conditional Formatte that can highlight a cell (group of cells) in a sheet based on the time of day ... For instance... if the starting time (cell C4) is 02:29 and the ending time (cell D5) is 04:30 then any time with in this range the cell should be high lighted. Any other time the cell is not. Scott S. A. Stone wrote: > I need to be able to right a conditional Formatte that can highlight a > cell (group of cells) in a sheet based on the time of day ... For > instance... if the starting time (cell C4) is 02:29 and the ending time > (cell D5)...

Excel cond. formatting
I'm looking to format a series of cells conditionally, ie if cell H7 reads "YES" then I want cells H1->H6 to all go green. Conditional formatting will allow me to do one cell, I know, but how do I extend this? I need this to happen automatically, not thru Macro's if at all possible. Cheers! GB Formula =$H$7="YES" for each of H1:H6 (can be done in one selection). -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "geordiebri" <geordiebri@yahoo.com> w...

More formatting woes
Have put together a 28 page chart including text paragraphs. Everything looks good in page layout, but when I go to print preview, many of my cells are clipping the text. Have tried to auto fit all rows, but no luck. Any help is appreciated. M. Hello M- Would you care to give us some idea of what version of what application in the Office Suite you're dealing with? I'm guessing some version of Word, but since you posted to the Office group rather than to the Word group one can't be sure. More detail on the issue would also be quite helpful... Remember that the folks in the ng ...

Conditional formating more that 3 cells
In need help. The conditional formatting I want goes in cells A1 to A10 and also M1 to M10. The conditions in cell A1 is: If the text in cell D1 is "ant" the cell color in A1 should be Yellow If the text in cell D1 is "noa" the cell color in A1 should be Pale Blue If the text in cell D1 is "nop" the cell color in A1 should be Tan If the text in cell D1 is "vac" the cell color in A1 should be Pink If the text in cell D1 is "req" the cell color in A1 should be Bright Green If the text in cell D1 is "off" the color in A1 should be Pl...

Copy any ROW that has RED background to Sheet2
Good morning all, I have a project where I have about 2000 rows (Sheet 1) that from day to day, any number of the ROWS will change to RED background. It would be great if I had a macro that would inspect the 2000 or so ROWS and anything with a RED background would get copied to Sheet2. Any help would be appreciated. -Thanks try this. Sub Macro1() For Each c In [a1:a2000] x = Sheets("sheet2").Cells(65536, 1).End(xlUp).Row + 1 If c.Interior.ColorIndex = 46 Then c.EntireRow.Copy Sheets("sheet2").Cells(x, 1) End If Next End Sub "RayD" <rcd_net@hotmail.com> ...