running a macro on several sheets

HI,

I have a spreadsheet and I am trying to run a macro on all sheets.
Here is my code for the first sheet called U.S.

Sub whatColor()

    For counter = 5 To 15
       cell = Worksheets("U.S.")Cells(counter, 38)
        If cell = 0 Then
            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
= 0
        'Better than last year, better than plan'
        ElseIf cell = 1 Then
            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
= 4 'green'
        'Better than last year, below plan'
        ElseIf cell = 2 Then
            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
= 6 'yellow'
        'Below last year, below plan'
        ElseIf cell = 3 Then
            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
= 3 'red'
            Worksheets("U.S.").Cells(counter, 35).Font.ColorIndex = 2
        End If
    Next counter

My problem is that some of the other sheets have different column
values.  For example I need to reference columns 29 & 26 instead of 38
& 35 for sheets 2 through 6 and I am not sure how to do this except
for repeating the function for each sheet.  Thanks.
0
jseger22 (1)
12/14/2007 2:06:46 PM
excel 39879 articles. 2 followers. Follow

1 Replies
197 Views

Similar Articles

[PageSpeed] 29

j,

Try the version below - when you said sheets 2 through 6, I went on position, not sheet name.  If 
that is incorrect, post back:

HTH,
Bernie
MS Excel MVP

Sub WhatColor2()
Dim myC As Range
Dim myS As Worksheet
Dim myCol As Integer
Dim CellValue As Integer

For Each myS In Worksheets

   If myS.Index >= 2 And myS.Index <= 6 Then
      myCol = 29
   Else
      myCol = 38
   End If

   For Each myC In myS.Cells(5, myCol).Resize(11)
      CellValue = myC.Value
      With myC.Offset(0, -3)
         If CellValue = 0 Then
            .Interior.ColorIndex = 0
            'Better than last year, better than plan'
         ElseIf CellValue = 1 Then
            .Interior.ColorIndex = 4   'green'
            'Better than last year, below plan'
         ElseIf CellValue = 2 Then
            .Interior.ColorIndex = 6   'yellow'
            'Below last year, below plan'
         ElseIf CellValue = 3 Then
            .Interior.ColorIndex = 3   'red'
            .Font.ColorIndex = 2
         End If
      End With
   Next myC
Next myS

End Sub

<jseger22@yahoo.com> wrote in message 
news:974ed97f-6919-4c2d-979c-e8bebe0d7e6f@i12g2000prf.googlegroups.com...
> HI,
>
> I have a spreadsheet and I am trying to run a macro on all sheets.
> Here is my code for the first sheet called U.S.
>
> Sub whatColor()
>
>    For counter = 5 To 15
>       cell = Worksheets("U.S.")Cells(counter, 38)
>        If cell = 0 Then
>            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
> = 0
>        'Better than last year, better than plan'
>        ElseIf cell = 1 Then
>            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
> = 4 'green'
>        'Better than last year, below plan'
>        ElseIf cell = 2 Then
>            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
> = 6 'yellow'
>        'Below last year, below plan'
>        ElseIf cell = 3 Then
>            Worksheets("U.S.").Cells(counter, 35).Interior.ColorIndex
> = 3 'red'
>            Worksheets("U.S.").Cells(counter, 35).Font.ColorIndex = 2
>        End If
>    Next counter
>
> My problem is that some of the other sheets have different column
> values.  For example I need to reference columns 29 & 26 instead of 38
> & 35 for sheets 2 through 6 and I am not sure how to do this except
> for repeating the function for each sheet.  Thanks. 


0
Bernie
12/14/2007 3:31:44 PM
Reply:

Similar Artilces:

How to open a password protected powerpoint file by Excel macro
how to open a password protected powerpoint file by Excel macro? by the way,the cipher is known. Not an expert with PPT; but there is no way to check whether a presentation has got a password or presentations.Open do not have an option to pass the password. Try the below which worked for me....which uses Sendkeys.. Sub Macro() Dim pptApp As Object, strFile As String, strPassWord As String strFile = "d:\Presentation8.ppt" strPassWord = "password" Set pptApp = CreateObject("PowerPoint.Application") pptApp.Visible = True pptApp.Activate SendK...

cmd button on form to run report
I have a dba that keeps track of clients and progress notes. i have a report that runs from a query that will show the notes for each client within a given week (i have parameters for dates). i put a cmd button on my form that will run the report when clicked on. What i want is when the cmd button is clicked on and the dates are entered into the parameters that the report on shows the current record on the form example if i open the form and go to mickey mouse and click on the cmd button, i want it to run the report and only show me notes for mickey mouse. right now when i click o...

Can I run a histogram with non-numeric data in Excel?
Example: E01a, E01b, E02a, E03a would be the bins. Not directly, but you could easily cobble something together. The histogram created by the Analysis ToolPak is a custom bar chart based on a table. You could easily modify that table after creation to use your desired labels and count text frequencies by using COUNTIF(). Jerry QATegan wrote: > Example: E01a, E01b, E02a, E03a would be the bins. ...

Macro to copy from one source sheet to numerous destination sheets
Each row in my source worrksheet has data to be entered into many destination worksheets. I have tried the following: Windows(Source).Activate Range(row, 2).Select Selection.Copy Windows(dept & ".xls").Activate Worksheets("Info").Activate Range("C3").Select ActiveSheet.Paste My problem is that the "Range(row,2).select" command does not work. Can someone provide me with a command line which would work. Thanks You sould always qualify any Range() reference with the containing worksheet (and that with it...

Erroneous Macro Recording
Hi, Macro-recording doesn't seem to handle the German decimal point "," all too well. Using the Macro-Recorder when changing the unit in a Pivot table, the algorithm generates: Sub ChangeUnit() ' ' ChangeUnit Makro ' ' Tastenkombination: Strg+q ' With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "Mittelwert von Cost per kW") .NumberFormat = "#.##0,00 ""€/kW""" End With End Sub .... whereas is should generate ... Sub ChangeUnit() ' ' ChangeU...

Office 2004 (Stud & Tea) wont run on MacBook
Hey, If I run the Office Assistant off the CD the icon appears in my bar and bounces for a second, then just disappears. If I manually copy the files across, they do copy fine, but then when I go to run any of the Office components they do the same as the Office Assistant, they bounce for a second then disappear. Only MSN Messenger works. What is going on here? Any help would be greatly appreciated. Thanks, Gavin In article <1195026641.585185.40700@k35g2000prh.googlegroups.com>, jeej <gavin.handley@gmail.com> wrote: > Hey, > > If I run the Office Assistant off ...

Macro Help needed #2
I'm trying to record a macro but I'm having difficulty with a particular action. I'm trying to copy text from a particular cell, Lets say Cell A1, and then go to the "Sheet Tabs", currently labeled Sheet 1, and paste the text to rename the sheet. Can Someone tell me what I'm missing from the following macro to make this happen: Sub Macro() ' ' Macro Macro ' Macro recorded 8/18/2005 by Anthony Amato ' ' Range("A1").Select Selection.Copy Sheets("Sheet 1").Select Sheets("Sheet 1").Name = " &qu...

Form Runs Non-Existing Function
Access 2007 sp2. I've run the same function form the On Close of a form for years. Curious I wanted to see what the code was. A subsequent search of all 4 code modules did not turn up the function name. When I remove the function name form the On Close of the form the code does not run so I know the function is somewhere. I'm even showing hidden and system objects in the nav bar. In design view of the form when I click the ellipse button (...) next to the On Close the expression builder comes up with the function name in the top window. Please tell me I'm not going c...

Multiple Sheets Ceates Folder of Sheets & XML File?
Since installing MS Office 2003 last year I have noticed different behavior with working on multiple sheets. First, every new sheet I open or create causes a new instance of Excel to open. Standard Excel 2003 behavior? Or do I have something configured incorrectly? Next, I worked on an existing workbook named '200501.xls' yesterday. While adding data to that workbook I created 11 additional worksheets, renamed the tabs and saved it with the same filename. Tonight I looked at that file in Windows Explorer and saw that it is only 13 kb in disk size. I knew that could not be correct. ...

outlook would not run
hello, my outlook 2000 would not run when i open it. it will only go as far as showing the green msoutlook logo and will always hang up. ctr-alt-del says it's not responding. i tried uninstalling/repairing the program (and even tried to uninstall the whole msoffice program) but always get the same hang up result. can anyone help me? ferdz Try opening Outlook using the ".../CleanViews" switch. The link below will give you the instructions on how to use it. OL2000: Additional Command-Line Switches http://support.microsoft.com/default.aspx?scid=KB;en-us;197180& Also try...

Adv. Filter
Hi Whenever I do Advanced Filter >Copy to another location, Excel wouldn't allow me to specify the destination (i.e. Copy to:) on worksheet other than the one I'm currently working on. Isn't there a way around this? -- Maki @ Canberra.AU Yes, there is a way around this. You must start the operation from the sheet you want to be the destination sheet. See Debra Dalgleish's site for how-to with a video should you choose. http://www.contextures.on.ca/xladvfilter01.html#ExtractWs Gord Dibben MS Excel MVP On Tue, 12 Jan 2010 06:40:01 -0800, Mak...

Bypassing macro warning messagebox
A macro-containing workbook that I have prepared will be used by several other colleagues. I think the message box that appears upon opening the workbook is likely to throw some of them off. Instead of asking them to change their security settings, is there a way to disable this messagebox? I hope not, or it would be rather redundant wouldn't it(?). -- Regards; Rob ------------------------------------------------------------------------ "K. Georgiadis" <anonymous@discussions.microsoft.com> wrote in message news:18b5401c44b73$e012b0a0$a501280a@phx.gbl... > A macro...

Macros and security
I have check boxes in my excel sheet. When I click a box and save as (I think enabeling macros), the next time I open it, I cannot change the check unless I first go to the now appearing options and click enable. I tried to play with the Trust Center and macro settings but cannot get it right. Thanks! ...

Unhide Multiple Sheets at once
Excel 2002 SP3 Win XP HE Hi, I know how to hide multiple worksheets at once, but cannot seem to find a way to unhide *multiple* sheets at once. Anyone? stef Only through code. Sub unhide() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... First...create a backup copy of your original workbook. To create a General Module, hit ALT + F11...

excel macros #4
I have an excel spreadsheet that contains macros. I need to move this file to another computer, but when I copy the file it does not seem to copy the macros. What is happening and how can I move this file with the macros? Thanks, Shadmtn Are your macros in a different workbook i.e. personal.xls You probably want to back up all files in your XLSTART directory, and you might want to check out Backup your files, always take backups http://www.mvps.org/dmcritchie/excel/backup;htm --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: ht...

spread sheet blank
I sent an attached excel spreadsheet to several employee. They were able to open it fine. When they entered/updated the information and sent it back to me and the other employees, they can open it but when I tried to open the file, I get a blank page. I saved the attachment to the HD and still cannot open it. I forwarded the file to another employee and the person can open it fine. O/S Window XP office XP I can open other email attached excel files fine - except that particular file. - I like I said others can open it file. Any clue? I have updated office/ reinstalled as well. I...

how do i switch between multiple sheets in the same workbook (key.
Hi CTRL-PGUP CTRL-PGDOWN "Catalin" wrote: > You can also see different tabs or sections from the same workbook by opening up a second view of the workbook by clicking on Window/New Window. After doing this reduce the size of the :1 workbook and then reduce the size of the :2 workbook so that both views can fit on the screen. Then you can mouse click back and forth between the windows. "Catalin" wrote: > Window->Arrange works nice on 2 copies of the same workbook, too. However, I find it fights with autosave in excel 2000. "cvgairport"...

How do I plot data in Excel that is captured on separate sheets; .
Data is saved in Excel worksheets by date. I need to plot various data from each of the separate sheets on a summary sheet. That is, the summary sheet plots data across multiple worksheets. For example, Avg1 is on Sheet1, Avg2 on Sheet2, ... Then, on the summary sheet, I would like to plot the Avg1, Avg2, ... sequence. I have not found a simple way to do this. ...

How do I get a cell to read sheet label?
I'm working on a report card for my mom's school, and I need to set up a Macro Button that will Copy the test of that sheet's label & paste it into a specific cell. This is what I recorded, but I'm getting an error at "Active Sheet.Paste": Range("R2:W2").Select With Selection .HorizontalAlignment = xlGeneral .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False ...

saving multiple sheets as a web page
Hi, I can save multiple sheets ok, by going - File, save as web page, then entire workbook. The problem I'm having is extra rows and colums on each sheet have been converted into html - which make the size of each html sheet quite large. Is there a property or format I can change, that would tell excel not to convert the empty extra rows or colums for each sheet. Cheers, Maryann ...

Print current slide while in fullscreen without macro?
Hi I do a presentation for a large company. They have their PowerPoint settings on NOT allowing macros. Is there any way that I can print a slide in fullscreen mode without pressing ESC? Any short commands perhaps? Ctrl + P didn't work. I work in PPT 2007. Thanx! In article <AE2AD94B-A6FC-4002-85DA-2759A456C324@microsoft.com>, Eva S wrote: > Hi > > I do a presentation for a large company. They have their PowerPoint settings > on NOT allowing macros. Is there any way that I can print a slide in > fullscreen mode without pressing ESC? Any short c...

How can I run a spreasheet over range of input data ?
I have set up a work sheet that takes input data (a number) from a cell, goes through all kinds of calculations based on other cells, and finally puts out a results in a output cell. Using this sheet, how can I sweep the input data over a range (min,max] and record the output data over the sweep? Thanks for any hint. -- Bernd ...

An easy way to changing refrence on several charts
How do I change referencec/range in a quick way on several chartssheets i a woorkbook I designed this utility to work on embedded charts. Perhaps you could adapt it to chart sheets. http://peltiertech.com/Excel/Charts/ChgSrsFmla.html - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "Henning" <Henning@discussions.microsoft.com> wrote in message news:88DD2F0E-AE63-4F6D-AE05-1D4699047693@microsoft.com... > How do I change referencec/range in a quick way on several chartssheets i > a > woorkbook Tri...

Protect & Hide Sheet
Hi.. I have a few questions : 1. I have a sheet which has data in rows. How can I have the row highlighted in red if a particular cell A3 is blank for example whereas cell A1 has the required data in it? 2. Is it possible to protect only 1 sheet of a workbook? I have mutliple sheets in a workbook which has to be updated and mailed but I don't wan't the recepient to see any data on a particular sheet only. I can hide the sheet but anyone can open that sheet be unhiding that sheet. 3. I have a shared worksheet where alot of people update the sheet as required. How...

wpf user control
using VS 2008 sp1 and c#, I have a user user control (uc1) which uses another user control (uc2) in its xaml. When I compile the application and then view uc1 in the designer, I get the error screen "Problem Loading" and a blue squiggly under the reference to uc2 in the xaml and the tooltip for the blue squiggly says "The type 'uc2' was not found...". In the constructor of uc2 I have replaced the old exception handling with some new exception handling and this problem began when I started using the new exception handling. evidently id doesn't li...