help with a sub

Hi,
can anybody tell me why the following code fails at FormatConditions.Add 

Private Sub CommandButton1_Click()

Dim Sh As Worksheet
Dim lngLastRow As Long

Set Sh = ActiveWorkbook.ActiveSheet
  lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
     Range("A4:E" & lngLastRow).Activate
       Selection.FormatConditions.Add Type:=xlExpression, 
Formula1:="=(MOD(ROW(),2)=0"
          Selection.FormatConditions(1).Interior.ColorIndex = 24
End Sub

Thanks
-- 
Traa Dy Liooar 

Jock
0
Utf
5/13/2010 4:15:02 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
434 Views

Similar Articles

[PageSpeed] 59

You have an extra open paren just before MOD:

"=(MOD"  should be "=MOD".

HTH,

Eric

"Jock" wrote:

> Hi,
> can anybody tell me why the following code fails at FormatConditions.Add 
> 
> Private Sub CommandButton1_Click()
> 
> Dim Sh As Worksheet
> Dim lngLastRow As Long
> 
> Set Sh = ActiveWorkbook.ActiveSheet
>   lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
>      Range("A4:E" & lngLastRow).Activate
>        Selection.FormatConditions.Add Type:=xlExpression, 
> Formula1:="=(MOD(ROW(),2)=0"
>           Selection.FormatConditions(1).Interior.ColorIndex = 24
> End Sub
> 
> Thanks
> -- 
> Traa Dy Liooar 
> 
> Jock
0
Utf
5/13/2010 5:22:01 PM
See other replies for the error fix.

If you want the banding to stick when sorting or filtering use this formula.

=MOD(SUBTOTAL(3,$A1:$A$2),2)=0


Gord Dibben  MS Excel MVP

On Thu, 13 May 2010 09:15:02 -0700, Jock <Jock@discussions.microsoft.com>
wrote:

>Hi,
>can anybody tell me why the following code fails at FormatConditions.Add 
>
>Private Sub CommandButton1_Click()
>
>Dim Sh As Worksheet
>Dim lngLastRow As Long
>
>Set Sh = ActiveWorkbook.ActiveSheet
>  lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
>     Range("A4:E" & lngLastRow).Activate
>       Selection.FormatConditions.Add Type:=xlExpression, 
>Formula1:="=(MOD(ROW(),2)=0"
>          Selection.FormatConditions(1).Interior.ColorIndex = 24
>End Sub
>
>Thanks

0
Gord
5/13/2010 11:24:12 PM
Thanks for the heads up on the par.
Didn't sort it tho.
I still get "Object defined or Application defined error" at the same point.
I recorded a macro whilst applying banding and adapted it to search for the 
last row which will change. I must be missing something obvious!

-- 
Traa Dy Liooar 

Jock


"EricG" wrote:

> You have an extra open paren just before MOD:
> 
> "=(MOD"  should be "=MOD".
> 
> HTH,
> 
> Eric
> 
> "Jock" wrote:
> 
> > Hi,
> > can anybody tell me why the following code fails at FormatConditions.Add 
> > 
> > Private Sub CommandButton1_Click()
> > 
> > Dim Sh As Worksheet
> > Dim lngLastRow As Long
> > 
> > Set Sh = ActiveWorkbook.ActiveSheet
> >   lngLastRow = Sh.Cells(Cells.Rows.Count, "A").End(xlUp).Row
> >      Range("A4:E" & lngLastRow).Activate
> >        Selection.FormatConditions.Add Type:=xlExpression, 
> > Formula1:="=(MOD(ROW(),2)=0"
> >           Selection.FormatConditions(1).Interior.ColorIndex = 24
> > End Sub
> > 
> > Thanks
> > -- 
> > Traa Dy Liooar 
> > 
> > Jock
0
Utf
5/14/2010 9:28:01 AM
Reply:

Similar Artilces:

Sub-menus of main menus for Excel 2007
When I click the main menu the sub-menus apprear but they are not highlighted. How can I make it highlighted so I can use it? Any particular menus? All menus? What is "Main Menu"? Is the worksheet protected? Gord Dibben MS Excel MVP On Mon, 15 Mar 2010 20:10:01 -0700, Eleanor Peppard <Eleanor Peppard@discussions.microsoft.com> wrote: >When I click the main menu the sub-menus apprear but they are not highlighted. >How can I make it highlighted so I can use it? ...

Search help needed
Access 2003 search form frmSearchDonors will have 1 field "txtSearchPhones" it will also have one button that is meant to open a form with the donor record containing that phone number in any one of its 3 Phone fields (Lets assume for a minute that no phone number can have duplicate donors). Phone fields are: phone_1 phone_2 phone_3 This is what I have but it is not working Dim stDocName As String Dim stLinkCriteria As String stDocName = "frmEditDonor" stLinkCriteria = "[phone_1]=" & "'" & Me![txtSearchPhones] & " &...

Microsoft Access Help Function Sample
With the help of Alex Dybenko and Stuart McCall I have created a help function for Access that uses an internal table to store help information rather than the traditional Access help file. The benefits are that you do not have to prepare and maintain an Access help file or buy software to create it. It can also be edited by users if you allow it. An explanation is available at http://www.projectperfect.com.au/microsoft-access-sample-8.htm and you can download the sample database from the same web address. Please send any feedback for improvements. Interesting. Simple an...

Visio Help Pls?
I am new to Visio. I have several questions and can't seem to find answers to. It is hard to find books on Visio 2003. I hope you can help. I have an org chart that I need to pubish to company website. I am trying to understand how this works. According to Visio 2003, I can save as webpage and when I do this I get a webpage to view. If I were to publish this page, I would need acces to publishing to my Company's Website. Do I need any other programs to be able to pubish? For instance Unix? or Dreamweaver? What are the steps when publishing an org chart? Can som...

Help screen for VBA in A2007
I have been trying to use VBA in Access 2007. I need the help function often and find it non-existent in A2007-all they give you is some useless macro items. Is there a way to get the the great help screen that you could get in A2000? Or, is there some other place on the web where you can enter something very general and get a list of possible answers? Google! Seriously, I agree that MS did something to 2007 help. Google is almost every developers best friend regardless of their programming language. For crying out loud, Google is a better search tool for this very for...

Need help data consolidation multiple workbooks
Tom Ogilvy was gracious enough to post the following piece of code which I can modify to do pretty much what I want. However, I would prefer to actually open each workbook using the "getopenfilename multiselect=true method" as I need to perform some other actions on the files before consolidating them. Since I haven't dealt much with arrays I'm a bit confused about how to modify the pieces of code that have to do with the array. Following is the piece of code. Would appreciate any input. Thank you!: Sub Totals() Const MAXBOOK As Long = 20 Dim i%, SheetArg$() ...

REQ:::: Outlook express v AOL... HELP
Hi can anyone tell me how I can use outlook express to read newsgroups. I have recently signed for AOL b/band and hate the AOL newsgroup reading system. ta in advance Jimbo Jimbo the lucky prophet wrote: > Hi can anyone tell me how I can use outlook express to read > newsgroups. I have recently signed for AOL b/band and hate the AOL > newsgroup reading system. > > ta in advance > > > Jimbo This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Windows PCs. For Outlook Express (OE) support try posting in one of these news...

Calculation Help Needed
A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs), and the shifts repeat after they complete. Shift 1 to 4 is Monday to Thursday, so the following Shift 1 to 4 is Friday to Monday and this continues on a 4-day cycle. If an employee works only on shift2, how do you calculate the number of days from shift3 until his scheduled shift2. Thanks Greg On Jul 27, 11:37=A0am, "Greg (code...@gmail.com)" <code...@gmail.com> wrote: > A company has 4-shifts (ie 1,2,3,4) and each shift is 1-day (24hrs), > and the shifts repeat after > they complete. Shift 1 to 4 ...

Help with SMTP addresses please
I have two domains A.int and B.A.int (child domain). 1 Exhcange (2003) server in domain A. A small set of users have accounts in both A and now B. Many with the same username. Accounts in both domains have mailboxes on the same exchange server. I can see all mailboxes in ESM. .. I added a recipient update service policy for B with the intention that users would get the SMTP domain from the exchange server (A.com). I saw briefly that usernames in B had the SMTP address user@b.a.com is this expected behavior? Our recipient policy defines A.com for SMTP. Also since SMTP address user@A.com is...

SQL Quotes help
Hello, I'm trying to write the WHERE part of a multi-part SQL statement IN VBA and the quotes within the DMin function are driving me mad! I have spent hours to no avail! I have searched this discussion group and cannot find a single example of a Domain Aggregate function within a VBA string SQL statement. The following is from the Query design SQL view which is what I want, but formatted as a string variable in VBA. (I added the stSQLw1= and opening/closing quotes.) stSQLw1 = "WHERE (((ClientAssignment.CASequence)=DMin("[CASequence]","ClientA...

Excel Help Please #2
I was just wondering whether or not you can change the size of the columns part way down the page without changing the ones at the top? Do you have any ideas? Also, I am hoping to be able to validate the numbers that can be entered into certain cells from allowing decimals between 2.5 and 10, to actually specifying each number, which wouldn't allow 2.7 from being entered. Unfortunately with my current validation, you would be able to add 2.7 which isn't a choice. I would apprecaite any insight. Thanks. The column width is exactly that ... the column width. If you want to cheat ...

SUMIF Formula Help
Hi Folks: I have a small table (C4:G85) Col. C is part numbers D-E-F are quantities Col. G is total quantity I'm trying (with no luck) to get a formula that will add the qtys of all part numbers that contain CU16 I'm using (entered as array) =SUM(IF(C4:C79="CU16",G4:G79)) Would appreciate any guidance. Many thanks. Steve Your formula looks ok to me, but I think I would have used: =SUMIF(C4:C79,"CU16",G4:G79) (Not array entered) But if your formula fails, then mine will too. Are you sure that you have a cell in c4:c79 that contains CU16 (no leading/trailin...

simplyfy and help on errors
Hello All, I have a formula that is almost working. I am trying make sure that if two specific criteria are met that the revenue column(f in this case) gets multiplied by K1 or K2 depending on the evaluation. I am hoping to simplify the following and be error free. =IF(OR(C50="",D50=""),"",IF(D50="Services A",F50*$K$1,IF(AND(SEARCH("Company A",C50),D50="Services B"),F50*$K$2,IF(D50="Services B",F50*$K$1,"")))) this is returning an Value Error any suggestions on how to go about this. This portion of you...

Dynamic Offsetting, Names, Arrays, Range, Chart Values. NEED HELP
Hi, I would like to enter values into a Chart from a Named Array(Range). The Array should not be an actual Range, but a virtual array based on logic applied to a Range. In my case picking values in ColumnB based on values in ColumnA Ideally I woud like to write something like: Chartvalues:= OFFSET(ColumnA;INT(ColumnA/3);1) and then make a Chart out of it. (I actually want to do something more complex: a variable-width bar chart using an X/Y scatter, w/o using Bullen's method) My problem is that ONE offset is applied on the whole array, and not DYNAMICALLY to the different items. Need...

Autofit column Help?
Hi there, The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub Hi Tim, Try the following Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Dim ws As Worksheet Set isect = Application.Intersect _ (Target, Columns(&...

bell curve charts
Hello all, I'd like to know how to make a bell curve chart, which when given a single monetry amount and a time line, can disperse the amount over the specified time line in a bell curve. Any help would be much appreciated. :) A wrote on Tue, 1 Sep 2009 19:15:01 -0700: > I'd like to know how to make a bell curve chart, which when > given a single monetry amount and a time line, can disperse > the amount over the specified time line in a bell curve. I don't think you are providing sufficient information or perhaps you should try again to say what you want. A nor...

Help
I have an 80 line column with Yes/No answers (Y=yes and N=no), and I need to find the percentage that were marked Yes. For example, let's say there are 63 Yes and 17 No answers. That would be 78.75% were marked as Yes. Excel XP SP2 -- try this =COUNTIF(A1:A1000,"yes")/COUNTA(A:A) format it as percentage. the data is in column A =================== "Dude" <liberaldemocrat@mhtc.net> wrote in message news:KoOdnTr1krg6dGXZnZ2dnUVZ_o-dnZ2d@bright.net... >I have an 80 line column with Yes/No answers (Y=yes and N=no), and I need >to find the percentag...

Please Help! Outlook Task list as HTML?
I urgently need help figuring out a way to convert, publish, export, etc., my Outlook task list as a HTML web page. I need to be able to retain all of the view characteristics of the original task list, i.e., colors, columns, sort, etc.. Really would appreciate any suggestions as I need this for an online meeting Monday night for people outside of our organization who do not have access to our Exchange Server. THANKS!!!! Why not use Outlook Web Access??? You could do screen captures and/or print to PDF files? Do you have the ability to make PDFs? If all else fails, use PRINTSCR...

Help Needed
Good morning: This is an example what I am trying to achieve Col-A Col-C Row 1: Name Address Col-A Col-C Row 4: Name Search Address Search Note: The names & address will be hidden below the respective search Col's. I need to search by name (R-4 / C-A) or by address (R-4 / C-C) and have the corresponding name & address populate R-1 respectively AND THEN CLEAR THE SEARCH CELLS LEAVING THE RESULTS IN R-1. Please keep in mind I am not a programmer. Thank you in adv...

Need help formatting a cell.
Can such a request be done using Excel? I need to enter a time in Cell B18 of 2325 or 11:25pm from that time i need it to calculate that cell B17 is 15 minutes prior to B18 (2310 or 11:10pm) B16 is 20 minutes prior to B18 (2305 or 11:05pm) B15 is 45 minutes prior to B18 (2240 or 10:40pm) B14 is 1 hour prior to B18 (2225 or 10:25pm) B13 is 1 hour 10 minutes prior to B18 (2215 or 10:15pm) and so one...... When i will need to change cell b18 to another time.... I need all my cells to still have the same minutes prior to be calculated and deducted. Sorry if questions is compliacted and conf...

Help files...
Hi guys, Has anyone seen a good tutorials on making help files in VS2003? HTML is perfered. AliR. "AliR" <AliR@online.nospam> wrote in message news:44ef41a2$0$15172$a8266bb1@reader.corenews.com... > Hi guys, > > Has anyone seen a good tutorials on making help files in VS2003? HTML is > perfered. > Can you download a trial copy of RoboHelp or some other help tool? I use ForeHelp, but they went out of business several years ago. -- David Thanks David, I'll look into RoboHelp. Just to clarify I was looking for a tutorial on using the VStudio Help ...

Help with formula please.
Hi, I have the following formula set up on a sheet called 'Wow' =IF(Wow!A1="90 / 63 reducer", Wow!B1) What I would like to add (in plain speak) is if on Wow sheet, cell A1 says 90 / 75 reducer, enter B1 into sheet 4 cell A4. At the end of the day, what im after is the following: If on Wow sheet, cell A1 says '90 / 75 reducer', enter Wow sheet B1 figure into sheet 4 cell A20, but if on Wow sheet, cell A1 says 'sleeve', enter B1 figure into sheet 4 cell A50, but if on Wow sheet, cell A1 says 'outlet', enter B1 figure into sheet 4 cell A100. I hope ...

Custome number format help
I have a series of cells that will hold values such as 350,000.00. This value is entered by the user and entire amount is used in calculations across the worksheet. I want the output to be 350k. This way, more columns can be viewed at a time and makes for easier reading than a number with all the zeros. I can't get the customer number format to "hide" the zeros. WB Thanks, yes it did. "Carmen" <hovendic@yahoo.com> wrote in message news:Od0aSCQrGHA.4356@TK2MSFTNGP02.phx.gbl... > WB, I put > > #,k > > in the custom formatting box and it looks ...

Macro Help... why does this error out?
This macro exports some info from excel to notepad... it'll right about 4 records successfully then it'll give me a "Run-time error'5': Inavalid procedure call or arguement" on the line of code that reads "CellText = ..Text & Space(ColWidth - Len(.Text))" If someone has some spare time or is bored please take a look and see what i have to do to fix this problem. Function WriteFile(delimiter As String, quotes As Integer) As String ' dimension variables to be used in this function. Dim CurFile As String Dim SaveFileName Dim CellText As String Di...

Help with Merging
Is it possible to merge workbooks so that I can take data from them all to make a chart? If so how is this done? I have a workbook for every month and need many numbers from each workbook to make some charts to see what progress we are having. Any help would be great! You should just be able define your data range regardless of whether it's in the same workbook or not. You can have multiple sets of data be drawn into one chart, from several workbooks. First, open up the workbooks you want to draw data from (in addition to the one where you will have the graph). Start the Graph...