# Excel Irregular Banding on a Filtered List by David Hager

Yesterday I was looking up some information about Excel banding on the Net, and I came across this article on Dick Kusleika’s web site.

http://dailydoseofexcel.com/archives/2006/06/30/irregular-color-banding/

Although he had cited me when he used a formula I created about 20 years ago as part of his banding solution, I never saw the article because he had misspelled my name (Hagar instead of Hager) so it never came up in a search.

=ROUND(MOD(SUM(1/COUNTIF(\$A\$3:A3,\$A\$3:A3)),2),2)=1

Anyway, I thought that his formula was very cool and it got me to thinking about how the same feature might be applied to a filtered list. I quickly realized that another formula for counting unique items would have to be used, since the “range” argument in the COUNTIF function has to be an actual range and cannot be an array. Luckily, I was able to remember that I had created another formula long ago to return unique items and this formula could accept arrays. Furthermore, I had already adapted this formula to work with a filtered list by using the SUBTOTAL formula invented by Laurent Longre shown at the link below (my old EEE publication on John Walkenbach’s site).

So, I modified the SUBTOTAL formula shown below to include expanding ranges as per Dick’s formula.

define filtRange as:

=IF(SUBTOTAL(3,OFFSET(\$B\$2:\$B2,ROW(\$B\$2:\$B2)-MIN(ROW(\$B\$2:\$B2)),,1)),\$B\$2:\$B2,””)

(where column B contains sorted information organized in blocks)

and replaced the “unique formula” used in his formula with one from the EEE article to come up with a truly powerful formula.

=ROUND(MOD(SUM(N(IF(ISNA(MATCH(“”,filtRange,0)),MATCH(\$B\$2:\$B2,\$B\$2:\$B2,0),IF(MATCH(filtRange,filtRange,0)=MATCH(“”,filtRange,0),0,MATCH(filtRange,filtRange,0)))=ROW(\$B\$2:\$B2)-MIN(ROW(\$B\$2:\$B2))+1)),2),2)=1

Caveat: In fact, it is so powerful that it takes up an enormous amount of computing power when used on a large list, so use it with care.

It can be used directly as a conditional formatting formula or added as an additional column in the list, which would then be used with the conditional format (i.e. – =\$D2).

As an example, the figure below would be the result before filtering: and the next figure would be the result after filtering (5 & 7 filtered from result column): I hope that this provides another useful bullet to your Excel formatting arsenal.

Hosted on John Walkenbach’s web site, here is a chapter on conditional formatting (text and workbook) that I wrote to be published, but it never made it into a book. Although now some 15 years old, it contains a large number of useful techniques that are still applicable today.

# My Own Favorite Quotes #1

The bane of every Excel developer is to hear “I guess I should have mentioned that…”.

# Scaling in-cell charts with an Excel formula

 You can scale   “in-cell charts” by using the following formula: where: data_point is the relative reference of   the 1st point in the data_range data_range is the absolute horizontal   reference of the data this_cell is the relative reference of   the cell containing the formula Formula is not completely volatile, so   you must press F9 after adjusting column widths. Scaling is not perfect (zooming affects it) but works OK. =REPT(“|”,(data_point*100/MAX(data_range))*CELL(“width”,this_cell)/(14+NOW()*0))

# An Oldie But A Goodie

Here is an oldie but a goodie to start the New Year. If you still use xlm functions in your apps, you can use this code to run them in VBA. Happy New Year!

Function RunXLM(fname As String, bBuiltIn As Boolean, ParamArray xArg())

Dim n As Integer

Dim num_arguments As Integer

num_arguments = UBound(xArg)

With Application

If bBuiltIn Then

Select Case num_arguments + 1

Case 0

RunXLM = .ExecuteExcel4Macro(fname)

Case 1

fname = Mid(fname, 1, Len(fname) – 1) & “,”

RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “)”)

Case 2

fname = Mid(fname, 1, Len(fname) – 1) & “,”

RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “,” & xArg(1) & “)”)

Case 3

fname = Mid(fname, 1, Len(fname) – 1) & “,”

RunXLM = .ExecuteExcel4Macro(fname & xArg(0) & “,” & xArg(1) & “,” & xArg(2) & “)”)

Case Else

RunXLM = “”

End Select

Else

Select Case num_arguments + 1

Case 0

RunXLM = .Run(fname)

Case 1

RunXLM = .Run(fname, xArg(0))

Case 2

RunXLM = .Run(fname, xArg(0), xArg(1))

Case 3

RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2))

Case 4

RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2), xArg(3))

Case 5

RunXLM = .Run(fname, xArg(0), xArg(1), xArg(2), xArg(3), xArg(4))

Case Else

RunXLM = “”

End Select

End If

End With

End Function