Add text to cell on condition across the workbook

[Apple08]

Hi everyone

I want to add a text 's' to column G if the value of cell in column J is 'New'. And I want to apply the vba across the entire workbook.

I have done the code below but I have no idea how to apply it to the whole workbook. Also I may have done the code wrong as there is nothing happened when I run the macro. Please could anyone help please.

Sub Add_S()
 Application.ScreenUpdating = False
 
 Dim ws As Worksheet
 Dim x As Long, LastRow As Long
 
 LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    For x = LastRow To 1
    
        If Cells(x, 10).Value = "New" Then
            Cells(x, 7).Value = "s"
        End If
       
    Next x
 
 End Sub

 

[mumps]

Try:

Sub Add_S()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Dim x As Long, LastRow As Long
    For Each ws In Sheets
        LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
        For x = 1 To LastRow
            If ws.Cells(x, 10).Value = "New" Then
                ws.Cells(x, 7).Value = "s"
            End If
        Next x
    Next ws
    Application.ScreenUpdating = True
 End Sub

 

[davesexcel]

I assume you need to count the rows in column J not column A

Loop through the sheets.

Sub Button1_Click()
    Dim sh As Worksheet
    Dim LstRw As Long
    Dim c As Range, Rng As Range

    For Each sh In Sheets
        With sh
            LstRw = .Cells(.Rows.Count, "J").End(xlUp).Row
            Set Rng = .Range("J1:J" & LstRw)
            For Each c In Rng.Cells
                If UCase(c) = "NEW" Then .Cells(c.Row, "G").Value = "s"
            Next c
        End With
    Next sh

End Sub

 

[Apple08]

Thank you both! They work perfectly!

 

[mumps]

Glad to help.