Code Procedure for multiple sheets

[AMY Z.]

Hi,
I have a procedure that formats Worksheet1 when something happens in a
certain cell. Worksheet1 is titled: APPLES. The procedure I have for this
works great.

Working part of the procedure I have now looks like this:

With Worksheets("APPLES")
Hide rows, change font color etc.

I would now also like to have the same thing happen in Worksheet2.
Worksheet2 is called ORANGES. Something like: With Worksheets("APPLES &
ORANGES")....

Is is possible to have mutiple worksheets follow the same procedure?

Thank you for your time,
Amy

 

[George Nicholson]

*Method one
Dim varSheets As Variant
Dim i as Integer

varSheets = Array("Apples", "Oranges")

For i = lbound(varsheets) to ubound(varsheets)
With Worksheets(varSheets(i))
'..........your code
End With
Next i

*Method two
Dim wks as Worksheet

For Each wks in Worksheets
Select Case wks.Name
Case "Apples", "Oranges"
With wks
'.........your code
End With
Case Else
'Do nothing
End Select
Next wks

HTH,

 

[Joel]

Here is one method

for each ws in worksheets
if (ws.name = "APPLES") OR (ws.name = "ORANGES") then
with worksheets(ws.name)
'put your code here

end with

end if

next ws

 

[AMY Z.]

Thank you for taking time to answer my question, Joel and Mr. Richardson.

I tried to change my procedure with the recommendations, but VB didn't like
it.

This is the complete procedure I use now that works:

Sub HideColRowsProcedure()

Dim myCell As Range
Set myCell = Range("H2")

With Worksheets("Sheet2") 'HIDE ROWS
.Rows("28:280").EntireRow.Hidden = False
If myCell.Text = "APPLES" Then ' Hide rows if H2 in sheet1
says APPLES
.Rows("29:40").EntireRow.Hidden = True
.Rows("43:56").EntireRow.Hidden = True

End If
End With
End Sub

I changed back to standard sheet names to make it easier to understand.
If you are in Sheet1 and enter "APPLES" in cell H2. It hides the two rows
in Sheet2.

I would like the procedure to also do the same thing in Sheet3 at the same
time.

Maybe this can't be done. I'm just learning VB.
Amy