Hi All,
I'm looking for a short piece of code to do the following:
Loop through my workbook, and change the tab color if cell F26 is <>0. I have the code for updating the color ok, but i'm struggling with the loop.
Here are a few questions/assumptions as well as the code (throws out of memory error) I tried and failed with:
1) Can I write one function for the updating of color, and pass it the appropriate worksheet name? what would that the two functions need to look like?
2) My list of worksheets that I want to ignore are in a variable called Ignore_Sheets with about 15 integers representing the sheet numbers to ignore
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim Ignore_Sheets(10, 13, 15, 2, 28, 33, 4, 49, 5, 68, 81, 83, 87, 88, 90)
IgnoreCount = UBound(Ignore_Sheets)
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
For J = 1 To IgnoreCount
If IgnoreCount.J = I Then Exit For
Next
With ActiveWorkbook.Worksheets(I).Name
MyVal = Range("f26")
Select Case MyVal
Case Is = 0
.Color = vbGreen
Case -1.5 To 1.5
.Color = vbYellow
Case Else
.Color = vbRed
End Select
End With
Next
End Sub
Thanks for the help in advance!
I'm looking for a short piece of code to do the following:
Loop through my workbook, and change the tab color if cell F26 is <>0. I have the code for updating the color ok, but i'm struggling with the loop.
Here are a few questions/assumptions as well as the code (throws out of memory error) I tried and failed with:
1) Can I write one function for the updating of color, and pass it the appropriate worksheet name? what would that the two functions need to look like?
2) My list of worksheets that I want to ignore are in a variable called Ignore_Sheets with about 15 integers representing the sheet numbers to ignore
Sub WorksheetLoop()
Dim WS_Count As Integer
Dim I As Integer
Dim Ignore_Sheets(10, 13, 15, 2, 28, 33, 4, 49, 5, 68, 81, 83, 87, 88, 90)
IgnoreCount = UBound(Ignore_Sheets)
WS_Count = ActiveWorkbook.Worksheets.Count
For I = 1 To WS_Count
For J = 1 To IgnoreCount
If IgnoreCount.J = I Then Exit For
Next
With ActiveWorkbook.Worksheets(I).Name
MyVal = Range("f26")
Select Case MyVal
Case Is = 0
.Color = vbGreen
Case -1.5 To 1.5
.Color = vbYellow
Case Else
.Color = vbRed
End Select
End With
Next
End Sub
Thanks for the help in advance!