Help with algorithm being stopped by find cells with red background

[viggen66]

Dear All,

I haven't coded for VBA for ages, and having some issue with this algorithm; I want excel to look up for free available leaves days an employee is entitled on previous months, to be offset with a current day on the current month, all unused available days are marked with string "P" and red background.

Sub Start()

  On Error Resume Next
 
        Dim WS_Count As Integer
        Dim ra As Range
        Dim rb As Range
        Dim I As Integer
        Dim strText As String
     
        strText = Range(Cells(Selection.Row, 2).Address).Value
                       
        WS_Count = ActiveWorkbook.Worksheets.Count

        For I = 1 To ThisWorkbook.Sheets.Count
        
        ActiveWorkbook.Worksheets(I).Select
    
        Set ra = Cells.Find(What:=strText, SearchFormat:=False)
       
        If Not ra Is Nothing Then

        ra.EntireRow.Select
   
        Application.FindFormat.Interior.Color = 255
     
        Set rb = Cells.Find(What:="P", SearchFormat:=True)
             
        If Not rb Is Nothing Then
       
        Call Found
       
        Else
       
        End If
        
        End If
          
Next I
End Sub

This code stops the execution of the algorithm in the first sheet, it ignores the loop "For I = 1 To ThisWorkbook.Sheets.Count"

The main goal is to after successfully, find both string "P" in a cell with red background in the "ra.entirerow.select", calls the module Found, which is basically a msgbox asking to change the background of activecell string "P" red to grey background, and hence , changing the initial day which was marked red, not available anymore for future leaves, but somehow the loop closes on first sheet.

Thanks

 

[6StringJazzer]

What module is this code in? If is it in the module for a worksheet, then all unqualified references to worksheet properties and methods will default to that worksheet instead of the ActiveWorksheet. And it will repeat the same action on the same worksheet over and over. It is a better coding practice to explicitly qualify all references.

In this line of code, what worksheet do you intend Cells to refer to?

   strText = Range(Cells(Selection.Row, 2).Address).Value

You are Selecting a row, but your Find method is looking in the entire sheet, not just that row. I have modified that.

Does this code run on different workbooks than the one containing the code? If not, then use ThisWorkbook instead of ActiveWorkbook. You used them both--you should consistently use on or the other.

Also you have a dangling Else. No harm, but not useful.

For starters I would make the revision below. Also, I recommend using indentation to show control structure and make your code easier to read.

Sub Start()

   On Error Resume Next
   
   Dim WS_Count As Integer
   Dim ra As Range
   Dim rb As Range
   Dim I As Integer
   Dim strText As String
   
   strText = Range(Cells(Selection.Row, 2).Address).Value
   
   WS_Count = ActiveWorkbook.Worksheets.Count
   
   For I = 1 To ThisWorkbook.Sheets.Count
   
      With ThisWorkbook.Worksheets(I)
      
         Set ra = .Cells.Find(What:=strText, SearchFormat:=False)
         
         If Not ra Is Nothing Then
         
            Application.FindFormat.Interior.Color = 255
            
            Set rb = ra.EntireRow.Find(What:="P", SearchFormat:=True)
            
            If Not rb Is Nothing Then
            
               Call Found
            
            Else ' dangling Else
            
            End If
         
         End If
         
      End With
   
   Next I

End Sub

 

[viggen66]

6StringJazzer

 strText = Range(Cells(Selection.Row, 2).Address).Value

is for the current sheet, for instance I want to insert string on cell 13, column 2 are the name of the coworkers, so Excel independently which cell you're in the entire row , strText gonna look up for the name of the coworker which is on column 2 on all sheets, each sheet is a month,

You're entirely correct, need to do some serious rewriting of the code.