running a macro on several sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jseger22@yahoo.com - 14 Dec 2007 14:06 GMT

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.

Reply to this Message

Bernie Deitrick - 14 Dec 2007 15:31 GMT

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

> HI,
>
[quoted text clipped - 28 lines]
> & 35 for sheets 2 through 6 and I am not sure how to do this except
> for repeating the function for each sheet. Thanks.

Reply to this Message