VBA - Content Banding

[hrayani]

Hi,

I am using the below code for content banding whenever a value changes in column B

Can anybody please amend the code to look at visible rows. Like when data is filtered.

Sub Colorize()

Dim r As Long, val As Long, c As Long


    r = 1
    val = ActiveSheet.Cells(r, 2).Value
    c = 34                                 


    For r = 6 To ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row
        If ActiveSheet.Cells(r, 2).Value <> val Then
            If c = 34 Then
                c = 19
            Else
                c = 34
            End If
        End If


        ActiveSheet.Range("A" & r & ":H" & r).Select
        With Selection.Interior
            .ColorIndex = c
            .Pattern = xlSolid
        End With
        val = ActiveSheet.Cells(r, 2).Value
    Next r
    
    End Sub

Any help would be appreciated.

Regards,

Humayun

 

[Peter_SSs]

Please have a look at it and let me know if the changes are OK - working perfect though

Looks fine to me.

 

[hrayani]

Thanks once again