April 19, 2005

Filter Highlight

When using AutoFilter, it's quite hard to see which column is being filtered.

This Custom Function can be used to highlight them. Enter the following code in a standard module. (Alt + F11, then Insert, Module from the top menu)

Function FilterOn(myCell As Range) As Boolean
    On Error Resume Next
    With myCell.Parent.AutoFilter
        With .Filters(myCell.Column - .Range.Column + 1)
            If .On Then FilterOn = True
        End With
    End With
End Function

Then using Conditional Formatting, enter =filteron(your cell reference). (Format, Conditional Formatting, Formula Is).

The result...

I should mention I made the above from tinkering with this custom function by Stephen Bullen (found on the Spreadsheet Page by John Walkenbach).

Always pays to tinker... ;-)  

Posted by andrewe at 19:42

April 06, 2005

Site Upgrades

Just a couple of quick announcements.

Colo has restyled his site, Colo's Excel Junk Room. Honestly speaking, his talent at design never ceases to amaze me, not to mention his skill at Excel of course. I wonder if there's a connection between the two? My artistic skills need some work, that's for sure ;-)

At the same time, the JMT Microsoft Excel Forum is being upgraded also. Unfortunately some former information and posts were lost. It's probably better that this happen sooner than later, some of the new features should prove worth it in the long run.

Please show us your support and join us there. You're always welcome!  
Posted by andrewe at 22:45

April 03, 2005

Row Highlight

Some time ago, I was experimenting with automatic row highlighting using VBA and thought a separate post might be in order just in case the previous comments got missed.

Using the Visual Basic Editor, place the following code in ThisWorkbook.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.ScreenUpdating = True
End Sub

Then, select your range with the mouse and open Conditional Formatting from the Format menu to enter this formula.


This is how it looks.

Quite simple and easy to remember. (The screen flickers a bit with larger files, it still seems to work without problems).

Ivan F Moala of the famed Xcel Files was kind enough to show me a good alternative. It does have some drawbacks but the highlighting factor itself is improved.

Note: It was also suggested to use =OR(CELL("row")=ROW(), CELL("col")=COLUMN()) to get both active row and column on my Japanese site. The choice is yours ;-)  
Posted by andrewe at 20:37