For each construct does not work

Hello all,

I am using a countif formula on a range in all sheets in a workbook (specifically range P2 to P500). If the occurence is >= 2 then I want to colour the background in the cell blue, however I want to do this accross all worksheets in the active workbook. For some reason it does not work, can anyone see what I have done wrong?

Sub duplicates()
Dim cell As Range
Const Colour = 5
Dim wks As Worksheet
Dim r As Range
Set r = Range("p2:p500")




Application.ScreenUpdating = False


For Each wks In ActiveWorkbook.Worksheets
For Each cell In r
Select Case cell.Value
Case Is < 2
cell.Interior.ColorIndex = xlNone
Case Is = ""
cell.Interior.ColorIndex = xlNone
Case Is >= 2
cell.Interior.ColorIndex = Colour
End Select
     Next cell
          Next wks
Application.ScreenUpdating = True
End Sub

:thanx:
Robert

Re: For each construct does not work

Quote from norie

The reason this doesn't work is because you only define r once and only for the active worksheet.

Try defining it within the loop and by referencing the worksheet.

Code

Sub duplicates() 
    Dim cell As Range 
    Const Colour = 5 
    Dim wks As Worksheet 
    Dim r As Range 
          
    Application.ScreenUpdating = False 
     
    For Each wks In ActiveWorkbook.Worksheets 
        Set r = wks.Range("p2:p500")     
        For Each cell In r 
            Select Case cell.Value 
            Case Is < 2 
                cell.Interior.ColorIndex = xlNone 
            Case Is = "" 
                cell.Interior.ColorIndex = xlNone 
            Case Is >= 2 
                cell.Interior.ColorIndex = Colour 
            End Select 
        Next cell 
    Next wks 
    Application.ScreenUpdating = True 
End Sub

Display More

Thank you Norie ...learnt a new thing today... :eureka: :thanx: