filtered rows getting shaded alternately (conditional formatting)

Dear Experts:

The following formula (=MOD(SUBTOTAL(3,$A$1:$A2),2) as a conditional
format keeps up alternate shading even if a list is filtered

Now I would like to use a macro solution to automate this process with
the following requirements:

- The List is in Sheet 1 and has the following name: 'Sheet 1!
DataList'
- The macro solution should allow for extension of the list below the
named range, i.e. a dynamic named range is to be used in the macro
- Alternate grey shading is to be applied

Help is much appreciated.

Thank you very much in advance.

Regards, Andreas
0
andreashermle
2/21/2010 5:48:05 PM
excel.programming 6508 articles. 2 followers. Follow

0 Replies
825 Views

Similar Articles

[PageSpeed] 20

Reply:

Similar Artilces:

Highlight Rows
Is there a way to program an expression close to... If cell in column E equals string of text "Pending", select entire row and change background color/patern to yellow? Hi, Conditional formatting. Select the row(s) to which this applies then Format|Conditional format - Select 'Formula is' from the dropdown and enter the formula =$E2="Pending" Click Format and on the 'Pattern' tab and choose yellow. OK Note that you should change the 2 to the top row of your selection -- Mike When competing hypotheses are otherwise equal, adopt th...

How to save spreadshhet in ASCII format?
This is a multi-part message in MIME format. ------=_NextPart_000_000E_01C48706.E76906F0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I am trying to save an Excel spreadshet in a format (I think it would be = ASCII) suitable for importing to another software package. The file = widths (fields) need to be exact. For some reason I am unable to find a = format in Excel that will work. I am using MS Office Pro 2002. =20 Thanks in advance for your assistance. WF ------=_NextPart_000_000E_01C48706.E76906F0 Content-Type: text/html; cha...

FRx 6.7: Accounts Not Displaying In Row File
I have successfully created Financial Statements for two of our three companies. However, I’m having a problem with the third one. In creating the row file, I specify a range from account 30000 through 99999. Most accounts show up in the list. Some don’t. The following account string will be my example of an existing account, with a balance, that does not show up in the row file: 00000-30035-00000 (30035 is the main account) If I manually insert a row into the row file, type in the description, and click the down arrow for the GL Link, the account does not display in the drop down. Ho...

Data format for pages
I want to change the format control properties "DataFormatAs" to a currency in Data Access Page (access 2000). Its asking for a code but I dont how?. Its easier in newer versions Select the Data Access Page > choose design > double click the field/cell whose format you want changed > Hit Design tab > Format > Change to Currency. -- HTP Adnan "Newguy" wrote: > I want to change the format control properties "DataFormatAs" to a currency > in Data Access Page (access 2000). Its asking for a code but I dont how?. Its > easier in newer ...

Blanks as a condition of a statement
How is it possible to do a function based on if a cell is blank or not. Such as the following for cell a1: IF b1 is not blank then add b1-c1 else don't do anything in a1. I have tried but can't get anything to go. Thanks in advance, Lee =IF(B1<>"",B1-C1,"") -- HTH RP (remove nothere from the email address if mailing direct) <Stuck on Blanks> wrote in message news:4383062f.5bc.41@news2... > How is it possible to do a function based on if a cell is > blank or not. Such as the following for cell a1: > IF b1 is not blank then add b1-c1 else d...

hide row/cols
How can I hide my row/col numbers, like the built-in Invoice Template does? Thanks, Andre Hi goto 'Tools - Options - View' -- Regards Frank Kabel Frankfurt, Germany Andre wrote: > How can I hide my row/col numbers, like the built-in Invoice Template > does? > > Thanks, Andre perfect - thanks! ...

Signature gets deleted
I create a message, insert a signature and click on which account to use to send the message. As soon as I select an account the signature gets deleted (rest of the message remains intact). If I create the message and select the account to use before inserting the signature all is fine. Any idea what is going on? PE, you wrote on Sat, 3 Mar 2007 16:46:54 -0000: > I create a message, insert a signature and click on which account to use to > send the message. As soon as I select an account the signature gets deleted > (rest of the message remains intact). > > If I...

Chart with merged rows
Another question for EXCEL gurus ! I have a chart with 56 rows (corresponding to 56 weeks in a year), each of which is composed of merging 7 rows (corresponding to 7 days/week). How can I get a graph with the values in these merged columns ? Selecting these and clicking the chart icon does not produce anything ! X-axis C, Y-axis - D -------------------------------------------------------------------------------- A B C D --------------------------------------------------------------------------------- 13. Aug. Sunday ...

Time Formating
I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as 12:52:11. For a possible solution see http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards, Peo Sjoblom "Denise" <anonymous@discussions.microsoft.com> wrote in message news:4804E8F2-C978-48DC-B163-67029B218F7F@microsoft.com... > I need to know how to format a cell for time. I need to have the cell formatted so that when I type in the cell say 125211 or 12 52 11 it will automatically appear as ...

How to get ID of a button & bitmap in a BitmapButton???
Hii all, I am using BitmapButtons in my application and I want to get the ID of the button aswell as the bitmap placed on it using a CWnd pointer to it.If this is not feasible please do help me with how to get a handler to button & bitmap so that I could use the GetDlgCtrlID() function to retrieve the button/bitmap ID’s. A big Thanxxxxxxxxxxxx in advance, Gov. ----- Well if you have a CWnd pointer to the Button. (I think you answered your own question on this one) pButton->GetDlgCtrlID(); or GetDlgCtrlID(pButton->m_hWnd); As far as the Bitmap ID goes, that information is not ...

API TO get User Name
What is an Win32 call to get the user name in the form of COMPUTER\\User ? >What is an Win32 call to get the user name in the form of COMPUTER\\User ? Have a look at GetUserNameEx NameSamCompatible Dave Hi Michael, Yes, just as David provided, GetUserNameEx will help you to retrieve the user or other security principal associated with the calling thread. If you got a different token other than the current calling thread, you may first call ImpersonateLoggedOnUser by passing the token to impersonate the token in the current thread and then use GetUserNameEx to obtain the user ...

Access2000: Setting a filter to a subform
Hi I have an unbound form fmMain. On this form I have some combo boxes (cbbAGroup, cbbAStatus, cbbAStore), and a subform sfAssets, which is based on form fmAssets. The source for form fmAsset is a table tblAssets with fields AssetID, AGroup,...,CurrStatus, CurrStore, ... etc. For subform sfAssets is defined the link cbbAGroup-->Agroup, i.e. the subform always displays assets of the single group, determined by combo on fmMain. Now 2 other combos must determine a filter condition for form fmAssets. The filter condition will be (in simplified form, not the real formula) Iif(cbbAStatus=...

keep getting error message when importing clipart
While in Publisher I am trying to import clipart and keep getting this error message: The appropriate graphic converter is not available. I have reinstaleld my software using the full install setting. I have downloaded all updates and it still won't work. Can somone help me? Clear your cache, in IE, tools, Internet Options, delete files. If that doesn't cure your issue, post back. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Karon" <Karon@discussions.microsoft.com> wrote in message news:6A2A64FD-B544-4...

Number of rows in a worksheet
I have a very large data file. It has 110,000 records approximately. want to import it into an excel worksheet. I did so using the Impor Text function and defining the field widths with !. Now the most I can seem to import onto a worrksheet is approximatel 65,500 records. The wizard then advises me to import the rest ont another data sheet, excluding the data already imported on to the firs sheet. When I try to do this, the wizard will only allow me to exclude th first 32,000 records from the second import and then will only impor approxiately 32,000 more records. In effect the second impor...

conditional formatting
I have a conditional format which the formula indicates true for column "A" and column "B" background shades red. how do I set up so columns "A-T" highlight red and set-up so all rows 1-400 are formatted when column "A" is true? I have tried and cannot get it to work...thanks Joe First select A1:T400, then use CF of =$A1 Bob Umlas Excel MVP "joe@malvern" wrote: > I have a conditional format which the formula indicates true for column "A" > and column "B" background shades red. how do I set up so...

How do you get rid of white borders that appear when you print?
I'm printing a full 8 1/2 by 11 flyer and want the color to print completely on the page. There is a graphic that has white in it so I need to have the printing put the background color. I have tried both to apply the color as a background and as a shape that is filled in but both times when I go to print I have a white border. I have adjusted the page size and the grid size to go to the edges. I can't find anywhere on the page set up menu where I can adjust the margins. Thanks This is a printer limitation. Newer Canon inkjets, some HP's and I'm not sure what el...

Can't get install to launch anymore
Hi, I had tried to do install earlier today of the CRM server. The install failed at some point where it seemed like it was trying to put data in the database. Everything seemed to be installed but I could not get access to the application. I tried to uninstall, this failed with an error of not much description. i wanted to try and re-install, but it could not get started, it was complaining that it get not determine my account name. I thought I polluted the system with all the other stuff that was going on, so I formated and started from scratch. I am not back to the point where I can in...

protect row color
I have a spread shhet that I have alternating row colors. If I do a data sort it moves the color along with the data. How do I protect the rows format so this does not happen Sounds as if the rows were coloured manually. Remove the colours, select the rows to shade using Format|Conditional Formatting. One way to specify every second row is MOD(ROW(),2)=0 Bernard "KIRK" <awm@cyberport.net> wrote in message news:1c4bd01c38850$83f15ba0$a601280a@phx.gbl... > I have a spread shhet that I have alternating row colors. > If I do a data sort it moves the color along with the &...

Script for changing users date format
Hi, Is there an easy way to change a bunch of users date format in one go? Thanks, Sophie Hi Sophie, Have you look into using workflow to update the data format field fo the users? Darren Liu, Microsoft CRM MVP Crowe Horwath http://www.crowecrm.com On Dec 16, 6:27=A0pm, Sophie <Sop...@discussions.microsoft.com> wrote: > Hi, > > Is there an easy way to change a bunch of users date format in one go? > > Thanks, > Sophie Hi Darren, Thanks for getting back to me. I can't find anywhere in the workflow an option to change the date format? Thanks, Sophie &qu...

How I can set highlighted active cell in row & colmun cross
I want to set active cell highlighted in row & colmun cross as it was possible in Lotus 123. Is it possible? if yes, how is it? Option Explicit '---------------------------------------------------------------- Private Sub Worksheet_SelectionChange(ByVal Target As Range) '---------------------------------------------------------------- Cells.FormatConditions.Delete With Target With .EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" With .FormatConditions(1) With .Borders(xlTop) ...

Formatting within this macro
I am new to VBA and can follow along fairly well, but don't know how to do certain things. I have the following macro that runs like a champ, but now I need to automatically format the SummarySheet. Specifically, I need to set Columns A,C,E and G to a width of 2 and to have a color of Light Gray (in Office 2010 it's White, Background 1, Darker 25%). I also need to set Rows 1 and 3 to a height of 6 and have a color of Light Gray. Then I need to change the output font to Tahoma, 12, Bold. Finally, I have to have a cell at the bottom of Column F that says "Totals&q...

File format is invalid
I have a file that I update and save daily. yesterday when I went t open it I got the following message: File Format Not Valid. This pop up in middle of the screen. the only option it gives me is a butto that says OK. I have tried to recover an old version of the file usin the windows recovery tool but I keep this file on an external driv which it does not recover. Any help with how to get this file bac would be greatly appreciated. Pau -- pserra ----------------------------------------------------------------------- pserra2's Profile: http://www.excelforum.com/member.php?action=ge...

Can you apply split colour formatting to the same cell?
Hi, I'm looking to apply more than one colour format to the same cell, split diagonally from right to left (one colour being orange, the other being white/uncoloured). I don't want stripes, just two blocks of colour making up the cell background. Slightly bizarre question, I know, but it's for a work diary for a department with colour representing 'busy' and white/uncoloured representing 'available'. As I'm busy for the first half of the day, I want to split the cell in half. At the moment I just have a diagonal line and the whole cell in orange, but wan...

Formatting "Caps" in a Style
Using Word 2007 Is it possible to include the changing of font case to Capitalize Each Word in a paragraph style? I would like the text to appear as: This Text Is In 'Capitalize Each Word' Case I would also like to avoid the use of Shift+F3, if I can. TIA David Word only has "paragraph" and "character" styles. There is no "word" style, which is what you would need to achieve what you are after. The following code will capitalize each word of the selected text: Selection.Range.Case = wdTitleWord -- Hope this helps, Doug R...

Create List/Add Row
Hello, I used the "Create List" function for a number of columns in a worksheet - but not all. I need to add a row, and I keep getting a message that says, "This operation is not allowed. The operation is attempting to shift cells in a list on your worksheet." I can add a row IN a list, but it only adds it in one column and doesn't carry it across the worksheet. It's when I try to add a row outside of a list that I get the error. Is there a way to correct this, so I can add/delete rows as needed? Thank you, in advance. Maybe you can convert e...