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

 

[footoo]

.

 

[Fluff]

How about

Sub Colorize()
   Dim val As Long, c As Long
   Dim Cl As Range
   
    val = Cells(1, 2).Value
    c = 34
    
    For Each Cl In Range("B6", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlVisible)
        If Cl.Value <> val Then
            If c = 34 Then
                c = 19
            Else
                c = 34
            End If
        End If
        
        With Cl.Offset(, -1).Resize(, 8).Interior
            .ColorIndex = c
            .Pattern = xlSolid
        End With
        val = Cl.Value
    Next Cl
    
    End Sub

 

[hrayani]

Hi Fluff,

Thanks for the reply...

Its not working on filtered data.

 

[Fluff]

In what way is it not working?

 

[hrayani]

Its coloring with every change in column B but its not looking at filtered rows (Visible rows). It just look at the entire data. I want the code to look at the visible rows.

 

[Peter_SSs]

Does this do what you want?

Sub BandVisible()
  Dim val As Variant
  Dim c As Long, r As Long
  
  c = 19
  val = Chr(1)
  For r = 6 To Range("B" & Rows.Count).End(xlUp).Row
    If Not Rows(r).Hidden Then
      If Cells(r, 2).Value <> val Then
        c = 53 - c
        val = Cells(r, 2).Value
      End If
      With Rows(r).Resize(, 8).Interior
        .ColorIndex = c
        .Pattern = xlSolid
      End With
    End If
  Next r
End Sub

 

[hrayani]

Hi Peter,

Thanks for the code. Its looking at the visible rows - Exactly what I wanted. Thanks.

Please also let me know which part of the code to ammend If I want the colors to be filled from column lets say Column C

Regards,

Humayun

 

[Peter_SSs]

Please also let me know which part of the code to ammend If I want the colors to be filled from column lets say Column C

From column C to where?

 

[hrayani]

From column C to where?

Column I

I tried changing this part of the code and it worked fine. I tried changing 8 to 9 and it added one more column. Column H to I

[COLOR=#333333]With Rows(r).Resize(, 8).Interior[/COLOR]

But I just wanted to know from where the Start Column is triggered in the code