PDA

View Full Version : Solved: Loop is not going through all worksheets



AnswerSeeker
03-14-2012, 11:03 PM
Hello,

I wrote a VBA code where cells are colored regarding some specific content. The code should be executed for all worksheets, but it is doing so only in the active worksheet.

Here the code:

Sub Color()
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets

Dim paint As Range
For Each paint In Range("A1:BB500")
If paint.Value = "MO" Then
paint.Interior.Color = vbCyan
ElseIf paint.Value = "FO" Then
paint.Interior.Color = vbGreen
ElseIf paint.Value = "BO" Then
paint.Interior.Color = vbRed
ElseIf paint.Value = "VR" Then
paint.Interior.Color = vbYellow
End If
Next paint

Next wkSht
End Sub


Hope someone can help me?!

frank_m
03-15-2012, 12:13 AM
Hi AnswerSeeker (http://www.vbaexpress.com/forum/member.php?u=44255)

welcome to the Vbax forum.

The reason your code only changes cells in the activesheet is because unless you select the next wkSht after each pass, Range("A1:BB500") is the active sheet range. The code needs to explictly define the next sheet by prefixing the range with wkSht.

As below:

Option Explicit
Sub Color()
Dim wkSht As Worksheet, paint As Range

Application.ScreenUpdating = False
For Each wkSht In ActiveWorkbook.Worksheets
For Each paint In wkSht.Range("A1:BB500")
With paint
If .Value = "MO" Then
.Interior.Color = vbCyan
ElseIf .Value = "FO" Then
.Interior.Color = vbGreen
ElseIf .Value = "BO" Then
.Interior.Color = vbRed
ElseIf .Value = "VR" Then
.Interior.Color = vbYellow
End If
End With
Next paint
Next wkSht
Application.ScreenUpdating = True

End Sub

[]

AnswerSeeker
03-15-2012, 12:59 AM
It works well.

Thank you very much frank_m! :)