Conditional based on filter 1

[travismallen]

I have a spreadsheet with 3 columns, item, cost, and diminished value. I was wondering how I could create a condtional that shows the diminished value only when the filter isn't showing all(the filter would be on items). I've never in VB for Excel so detailed info would be great. Thanks.
T

___________________________
"I am what I am" - Popeye
Travis M. Allen

http://www.trav-ism.com

 

[combo]

Hi Travis,
It is non-basic excel, but works by conditional setting of font colour.
First assume, that your table is in Sheet1 and starts in A1. Next, within the table in column A there are no empty cells and all cells below table in column A are empty.

Go Insert>Names>Define dialog and add two names:
1: RefRange
with formula (in refers to box):
=OFFSET(Sheet1!$A$1,,,COUNTA(Sheet1!$1:$1),1)
2: Crit
with formula:
=ROWS(RefRange)=SUBTOTAL(3,RefRange)
Next mark cells in column three, where you want to conditionally hide contents (it will be set to white text). With selected cells, go to Format>Conditional formatting dialog. You should be raedy to define "Condition 1" formatting. From the left drop-down select "Formula is" option, in the right box write condition: =Crit. Go "Format" button and select white colour for text. Accept formatting.
Now having all rows in the table visible will cause setting text to white.

With VBA it is even simpler - right-click the sheet's with list tab and select "View code". Copy the code into sheet's module:
[tt]Private Sub Worksheet_Calculate()
On Error Resume Next
Me.Range("_FilterDatabase&quot.Columns(3).EntireColumn.Hidden = Me.FilterMode
End Sub[/tt]

You need to trigger "Calculate" event, so if there is no expected effect, place somewhere formula =NOW()*0 to raise event.

combo

 

[combo]

An auto-correction to VBA code (to hide column when no filtered rows, instead of opposite effect):
Me.Range("_FilterDatabase&quot.Columns(3).EntireColumn.Hidden = Not Me.FilterMode

combo