Looping through multiple worksheet - Not working properly

[Madgik]

Dear Expert,


I am a newbie to VBA and wanted a bit of code to help me out with some excel files.


It nearly works. It processes and updates the 1st worksheet perfectly but does not process any other worksheets and I have no idea why!


My excel file has 4 columns and lots of worksheets (the layout is the same across all worksheets) :-


Column A - Contains a text description
Column B - Contains the =Len(A1) to give me the length of the string
Column C - Contains a French version of the English text in column A
Column D - Contains the =Len(C1) to give me the length of the French string




What I am trying to achieve is to compare the length of the French strings against the length of the English strings. If the French string is longer than the English then highlight the cell in Column D that is longer in red.


If anyone can help me workout what is wrong that would be great.


The code is as follows :-

Private Sub CommandButton1_Click()


Dim trans_len As Range
Dim Cell As Range


Dim I As Integer


WS_Count = ActiveWorkbook.Worksheets.Count




For I = 1 To ActiveWorkbook.Worksheets.Count


    Set currentsheet = ActiveWorkbook.Worksheets(I)
    Worksheets(I).Activate


    Set trans_len = Range("D2:D50")
    
    For Each Cell In trans_len
        If Cell.Value > Cell.Offset(, -2).Value Then
            Cell.Interior.ColorIndex = 46
        End If
    Next
    
    MsgBox ActiveWorkbook.Worksheets(I).Name
    
Next I


MsgBox "Finished", vbInformation




End Sub

Cheers
Madgik

 

[Fluff]

You can do it quite easily with CF, if you're interested.
But I have no idea why the macro doesn't work for you.

 

[My Aswer Is This]

Why do you if you do have formulas in all these rows?

A vba script can do all this with no formulas in the cells?

Do you have formulas in column B and D

See you said:

Column B - Contains =Len(A1) to give me the length of the string in Column A.

You should not need the formula.
Excel can do this for you
​

 

[Fluff]

Lets try this another way.

Private Sub CommandButton1_Click()
   Dim Cl As Range
   Dim i As Long
   
   For i = 1 To ActiveWorkbook.Worksheets.Count
      With ActiveWorkbook.Worksheets(i)
         For Each Cl In .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            If Len(Cl) <= Len(Cl.Offset(, 2)) Then
               Cl.Offset(, 3).Interior.ColorIndex = 46
            End If
         Next Cl
      End With
      MsgBox ActiveWorkbook.Worksheets(i).Name
   Next i
   
   MsgBox "Finished", vbInformation
End Sub

 

[Madgik]

If there is a way to do this easily with CF then I would love to know.

The excel sheet is massive and I don't want to individually format each row with a CF.

 

[My Aswer Is This]

Try this:
This script is not looking at formulas
The script knows how to do this with no formulas in the cells.

Sub Length()
'Modified  10/9/2018  1:39:09 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
For b = 1 To Sheets.Count
With Sheets(b)
    Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
        For i = 1 To Lastrow
            If Len(.Cells(i, 4)) > Len(.Cells(i, 2)) Then .Cells(i, 4).Interior.ColorIndex = 3
        Next
End With
Next
Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

Your subject title said:

Looping through multiple worksheet

My script assumes you mean all your sheets.
Other wise you will have to say what sheets

And if you change some of the values after running the script the script will not change the color formatting of those cells already formatted if the len changes
The next time you run the script

If you wanted that you would need to say so.​

 

[Fluff]

If there is a way to do this easily with CF then I would love to know.

The excel sheet is massive and I don't want to individually format each row with a CF.

Select D2 to the last row > Home Tab > CF > New Rule > Use a Formula > enter =D9>B9 select format > OK

 

[Fluff]

@MAIT
Why are you testing the length of cols B & D? They simply contain the =LEN(A1) formula.

 

[My Aswer Is This]

@MAIT
Why are you testing the length of cols B & D? They simply contain the =LEN(A1) formula.

Because I really do not think that is what he want.

I cannot really believe he wants to compare A1 on each sheet with the length of Each cell in column B with the length of ever cell in Column D

But it appears as of now neither of us have given him a answer he wants.