VBA - Content Banding

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
939
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,049
Office Version
  1. 2016
Platform
  1. Windows
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
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

Well-known Member
Joined
Jul 23, 2010
Messages
939
Office Version
  1. 2016
Platform
  1. Windows
Hi Fluff,

Thanks for the reply...

Its not working on filtered data.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,335
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

In what way is it not working?
 

hrayani

Well-known Member
Joined
Jul 23, 2010
Messages
939
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,235
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Does this do what you want?

Code:
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

Well-known Member
Joined
Jul 23, 2010
Messages
939
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,235
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jul 23, 2010
Messages
939
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,003
Messages
5,526,221
Members
409,688
Latest member
Mc Junior

This Week's Hot Topics

Top