vba run time error 424

[kelvin_9]

Hi All,

i need a vba to work multiple request as below but i get an error 424 at "For Each rng In ranges"
how can i fix?

i have 5 columns in "source" and i need "week1" effect these changes based on "source"

last but not least, if i have another sheet named "week2", i need same effect, am i simply just add this?

    Set ws3 = ThisWorkbook.Sheets("week2")

thank you very much for your answering

here is my code

Sub macro1()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim i As Long

    ' Set your worksheets
    Set ws1 = ThisWorkbook.Sheets("week1")
    Set ws2 = ThisWorkbook.Sheets("source")

    ' Define the ranges you want to format
    Dim ranges As Variant
    ranges = Array("D9:D200", "F9:F200", "H9:H200", "J9:J200", "L9:L200", "N9:N200", "P9:P200")

    ' Loop through each defined range
    For Each rng In ranges
        For Each cell In ws1.Range(rng)
            For i = 1 To 1000 ' Check against the first 1000 rows in Sheet2
                ' Check and apply formatting based on Sheet2
                If cell.Value = ws2.Cells(i, 1).Value Then
                    cell.Font.Bold = True ' Bold font size if condition met in Column A of Sheet2
                End If
                If cell.Value = ws2.Cells(i, 2).Value Then
                    cell.Font.Color = RGB(255, 0, 0) ' Red font color if condition met in Column B of Sheet2
                End If
                If cell.Value = ws2.Cells(i, 3).Value Then
                    cell.Interior.Color = RGB(0, 176, 240) ' Blue cell color if condition met in Column C of Sheet2
                End If
                If cell.Value = ws2.Cells(i, 4).Value Then
                    cell.Interior.Color = RGB(226, 239, 218) ' Green cell color if condition met in Column D of Sheet2
                End If
                If cell.Value = ws2.Cells(i, 5).Value Then
                    cell.Interior.Color = RGB(191, 191, 191) ' Grey cell color if condition met in Column E of Sheet2
                End If
            Next i
        Next cell
    Next rng
End Sub

 

[Peter_SSs]

I have not looked closely at all the code but that error should be fixed by changing ..

Dim rng As Range
Dim rng As Variant

 

[kelvin_9]

I have not looked closely at all the code but that error should be fixed by changing ..

Dim rng As Range
Dim rng As Variant

thank you very much for your answer, Peter_SSs

it works just prefect

thank you very much for your guidance

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.