# Thread: Conditional formatting based on reow visibility (97+)

1. ## Re: Conditional formatting based on reow visibility (97+)

Here is a VBA solution from Andrew <post#=69000>post 69000</post#>, in fact I found that whole thread to be extremely useful.

Cheers

2. ## Re: Conditional formatting based on reow visibility (97+)

Define this name:

Name:

IsVisible

Refersto:

=IF(GET.CELL(17,INDIRECT("rc",FALSE))=0,FALSE,TRUE )+0*NOW()

Now enter this formula in an empty column next to your list to be filtered:

=IsVisible

and fill down.

Now use this conditional format formula (assuming list starts in row 2 and the Isvisible formula is in column D):

=MOD(SUM(\$D\$2:\$D2),2)=0

Please note, that copying cells from column D to another sheet may crash Excel, due to the use of this special defined name!

3. ## Re: Conditional formatting based on reow visibility (97+)

Thanks Catherine,

I too found that an interexsting thread.
Unfortunately this suffers from the same ailment I am trying to cure.

The conditional formatting uses the row number to determine the background colour. Unfortuantely when the list is filtered, the rownumber does not change, what I was looking fro was a method to determine (without vba if possible) the row number based on lonly the visible cells.

It looks like Jan has the answer (again)

Thanks

4. ## Re: Conditional formatting based on reow visibility (97+)

Jan Karel

Thank you very much.

5. ## Re: Conditional formatting based on row visibility (97+)

Jan Karel,

I just figured out that the subtotal command will do the same thing, without having to resort to Excel 4 functions embedded in defined names.

Create a new column with the function =Subtotal(3, RangeStartCell(absolute referencing required), RangeEndCell(relative reference))
eg first cell (row 2) is =SUBTOTAL(3,\$A\$2:A2), last cell row 10 is =SUBTOTAL(3,\$A\$2:A10)
This column will then give you the row number of the cell and adjust automatically for the hidden rows.

Same result, but it means that the cells can be copied to another workbook without the necessity to copy the named range definition.

6. ## Re: Conditional formatting based on reow visibility (97+)

Jan

Will you please explain how the IF(...) in your name definition works

TIA

7. ## Re: Conditional formatting based on row visibility (97+)

Kieren

Your =Subtotal(3, RangeStartCell(absolute referencing required), RangeEndCell(relative reference))

should have a colon not a comma between RangeStartCell(absolute referencing required) and RangeEndCell(relative reference), you got it right later on.

Very clever.

You can combine the whole lot into a conditional formatting formula with no need for an extra column:

=MOD(SUBTOTAL(3,\$D\$2:\$D2),2)=0

8. ## Re: Conditional formatting based on row visibility (97+)

Thanks Michael,

I never thought of putting it all in the conditional format condition.

One change to your foumula is :="=MOD(SUBTOTAL(3,\$a\$2:\$a2),2)=0
(note the absolute/relative mix of referencing on the \$a2 to ensure that all columns are treated equally)

For the record, the macro I use now is :

Sub FormatData()
'
' FormatData Macro
' Assumes that the list is contiguous, rectangular, includes headings and has upper left corner in cell A1

On Error Resume Next
Application.ScreenUpdating = False

' remove underscore (Dumps from Oracle usaually have underscores in the column names)
Message ("Removing underscore from title ...")
Range("A1", Range("A1").End(xlToRight)).Select
Selection.Replace what:="_", Replacement:=" ", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
' Modify normal Style
Message ("Updating Normal Style ...")
With ActiveWorkbook.Styles("Normal")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
End With
' center and wrap title row
Message ("Centering and wrapping Title row ...")
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.ShrinkToFit = False
End With
' Internal gray colur for title
Message ("Setting Title background ...")
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With

' bold title row
Message ("Setting Title to Bold ...")
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

' Print settings (Repeat title on page, landscape, 1 page wide)
Message ("Setting Print Settings Title background (Repeat title on page, landscape, 1 page wide)...")
With ActiveSheet.PageSetup
.PrintTitleRows = "\$1:\$1"
.LeftFooter = "&A"
.CenterFooter = "&P of &N"
.LeftMargin = 53.8582677165354
.RightMargin = 53.8582677165354
.TopMargin = 56.6929133858268
.BottomMargin = 56.6929133858268
.FooterMargin = 36.8503937007874
.PrintGridlines = True
.CenterHorizontally = True
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.FitToPagesWide = 1
.FitToPagesTall = False
End With

' autofit
Message ("Autofit all data ...")
ActiveSheet.UsedRange.Select
Cells.EntireColumn.AutoFit

' colour alternate rows
Message ("Colouring alternate rows ...")
Selection.FormatConditions.Delete
Selection.FormatConditions(1).Interior.ColorIndex = 35

' freeze panes
Message ("Setting scroll/freeze panes ...")
Range("B2").Select
Range("B2").Activate
ActiveWindow.FreezePanes = True

' autofilter
Message ("Autofilter all data ...")
Selection.Autofilter

Message ("")
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

' display a message in the status bar
Sub Message(sMess As String)
Application.DisplayStatusBar = True
Application.StatusBar = sMess
End Sub

9. ## Re: Conditional formatting based on row visibility (97+)

Kieran

You were too quick, you replied to my post before I edited it to put the third \$ into \$D\$22

One problem I had with Andrew's original macro was losing existing conditional formatting when coloring the rows, as I often highlight certain values using conditional formatting. Now I can name "=MOD(SUBTOTAL(3,\$D\$2:\$D2),2)=0"
as ColAltRows and use F3 to insert this name as the third conditional format whilst keeping two existing conditional formats.

10. ## Conditional formatting based on reow visibility (97+)

<P ID="edit" class=small>(Edited by kieran on 08-Oct-02 12:55. To correct my poor typing)</P>This is just a wild stab in the dark...

I usually use conditional formatting in excel to colour alternate rows ( eg =MOD(ROW(),2)=0 ) where I am manipulating large lists.
I have got so lazy that this is automated, and then enables autofilter on the affected columns.

This is fine, however when I use autofilter, the conditional formatting does not recognise the 'hidden' status of the filtered rows, and the nice orderly effect of alternate shading is gone.

Now I am not usually an orderly person, but this got me thinking if there is a way to amend the conditional formatting function to allow for the filtered rows?

TIA

(PS - Thanks for everyone's patience with my typing errors )

11. ## Re: Conditional formatting based on reow visibility (97+)

Well, it is a bit overdone, using the IF clause.

This works equally well:

=NOT(GET.CELL(17;INDIRECT("rc";FALSE))=0)+0*NOW()

Clear as mud?

12. ## Re: Conditional formatting based on row visibility (97+)

Good idea!

I'll keep it in mind. It is good to avoid the XL4 macro functions when possible.

13. ## Re: Conditional formatting based on row visibility (97+)

Mike,
Note you may get oddities using this method if you have gaps in your data (since the counting will ignore them) which I haven't found a way round to date.
FWIW.

14. ## Re: Conditional formatting based on reow visibility (97+)

You said it.

1. XL help does not list GET except for GETPIVOTDATA.

2. I guess the 17 after CELL refers to info_type, what does it mean?

3. What does the +0*NOW() do?

15. ## Re: Conditional formatting based on row visibility (97+)

True Rory, though unless your data is very holey it is not likely to be a big problem as the colours are only to help to see lines of data across a big spreadsheet. If you add a new column A with anything in it (eg "a" in every cell) in EVERY row from row 1 to the last row with data, it overcomes the problem.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•