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]

Hi & welcome to MrExcel
How about

Private Sub CommandButton1_Click()
   Dim Cell As Range
   Dim I As Long
   
   For I = 1 To ActiveWorkbook.Worksheets.Count
      With ActiveWorkbook.Worksheets(I)
         For Each Cell In .Range("D2:D50")
            If Cell.Value > Cell.Offset(, -2).Value Then
               Cell.Interior.ColorIndex = 46
            End If
         Next Cell
      End With
      MsgBox ActiveWorkbook.Worksheets(I).Name
   Next I
   
   MsgBox "Finished", vbInformation
End Sub

 

[Madgik]

Hi,

Thank you very much for looking at this.

I copied and pasted your code into the excel file but it does not work. It displays the worksheet names but does not perform any of checking to see if one strings is greater then the other or highlight any longer versions in red.

I have no idea why.

Thanks again.

 

[Fluff]

What are the values in col B & D?

 

[Madgik]

This is what is contained in each column :-

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

I hope this helps.

 

[Fluff]

Are the sheets protected and do you have any merged cells?

As I get

Excel 2013/2016
	A	B	C	D
2	AL1 5EG	7	St Albans	9
3	AL10 9WX	8	Welwyn Hatfield	15
4	AL6 0PB	7	East Hertfordshire	18
5	AL6 9TU	7	North Hertfordshire	19
6	B14 7TW	7	Birmingham	10
7	B36 9UQ	7	Solihull	8
8	B43 7AN	7	Walsall	7
9	B43 7TB	7	Sandwell	8
10	B45 9XE	7	Bromsgrove	10
11	B46 1UG	7	North Warwickshire	18
12	B50 4QF	7	Stratford-on-Avon	17
13	B61 9HQ	7	Wychavon	8
14	B63 4PS	7	Dudley	6
15	B74 4UT	7	Lichfield	9
Sheet1

Cell Formulas
Range	Formula
B2	=LEN(A2)
D2	=LEN(C2)

 

[Madgik]

The sheets are not protected and there are no merged cells.

There are lots of worksheets, each worksheet is formatted in exactly the same way.

 

[My Aswer Is This]

Try this:

Sub Length()
'Modified 10/9/2018 12:18 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim b as long
Dim Lastrow As Long
For b = 1 To Sheets.Count
With Sheets(b)
    Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
        For i = 1 To Lastrow
            .Cells(i, 2).Value = Len(.Cells(i, 1))
            .Cells(i, 4).Value = Len(.Cells(i, 3))
        Next
End With
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Do you have any conditional formatting on the sheets?

@MAIT
The OP already has the length in Cols B & D & is trying to highlight some.

 

[Madgik]

Thanks for this. I don't really understand the code but i was about to say that i need to highlight some rows.

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

There is no conditional formatting. This a pain to setup and you would have to do it manually for every row in every worksheet.