How to find and apply format in multiple sheets in excel?-CodePudding

I would like to find the week number and apply formatting to that cell. The week number is generated automatically using weeknum formula in Sheets("Program").Range("N3").

I have 5 sheets. In 1st sheet Overview, the data is in row 8 and the formatting works. In sheet 2 to 5 the data is in row 4. So, I selected all 4 sheets and used the same logic. But the formatting is not working on sheet BBB, CCC, DDD. My expected output in image.

My program not showing any error and not working. Can any one help me?

Sub FindandFormat()  
    Dim ws1, ws2, ws3 As Worksheet
    Dim CW As String
    Dim rng2, rng1 As Range
    
    Set ws1 = ThisWorkbook.Worksheets("Overview")
    Set ws2 = ThisWorkbook.Worksheets("AAA")
    
    ' "Format to show the actual week in every sheet"
    CW = "W" & ThisWorkbook.Worksheets("Program").Range("N3").Value - 1
    
    With ws1
        Set rng1 = .Rows("8:8").Find(What:=CW, LookIn:=xlValues)
        With rng1.Interior
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.599993896298105
        End With
    End With
    
    With ws2
        Set rng2 = .Rows("4:4").Find(What:=CW, LookIn:=xlValues)
    
        ThisWorkbook.Sheets(Array("AAA", "BBB", "CCC", "DDD")).Select
    
        With rng2.Interior
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.599993896298105
        End With
    End With
End Sub

CodePudding user response：

Note that if you declare Dim ws1, ws2, ws3 As Worksheet only ws3 is of type Worksheet but the others are of type Variant. In VBA you need to specify a type for every variable or they are Variant by default: Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet. Same for your Range.

The issue in this code is that selecting those sheets does nothing but selecting them.

With ws2
    Set rng2 = .Rows("4:4").Find(What:=CW, LookIn:=xlValues)

    ThisWorkbook.Sheets(Array("AAA", "BBB", "CCC", "DDD")).Select

    With rng2.Interior
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
    End With
End With

You apply the format to With rng2.Interior and rng2 references to .Rows("4:4")… which uses With ws2 so clearly applies only to ws2! It applies it to ws2 no matter which worksheets are selected.

Instead you need to loop over your worksheets and apply the format to every worksheet:

Dim WorksheetNames As Variant  ' define the worksheet names you want the format to apply to
WorksheetNames = Array("AAA", "BBB", "CCC", "DDD")

Dim WorksheetName As Variant
For Each WorksheetName In WorksheetNames  ' loop through all worksheet names in the array
    Dim FoundAt As Range  ' try to find CW in each worksheet
    Set FoundAt = ThisWorkbook.Worksheets(WorksheetName).Rows("4:4").Find(What:=CW, LookIn:=xlValues)
    
    ' check if CW was found otherwise show error message
    If Not FoundAt Is Nothing Then
        With FoundAt.Interior  ' perform format change
            .ThemeColor = xlThemeColorAccent6
            .TintAndShade = 0.599993896298105
        End With
    Else
        MsgBox """" & CW & """ was not found.", vbOKonly
    End If
Next WorksheetName