Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    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
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Jan Karel

    Thank you very much.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Jan

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

    TIA

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    .Shadow = 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"
    .CenterHeader = "&F"
    .RightHeader = "&D &T"
    .LeftFooter = "&A"
    .CenterFooter = "&P of &N"
    .LeftMargin = 53.8582677165354
    .RightMargin = 53.8582677165354
    .TopMargin = 56.6929133858268
    .BottomMargin = 56.6929133858268
    .HeaderMargin = 36.8503937007874
    .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.Add Type:=xlExpression, Formula1:="=MOD(SUBTOTAL(3,$a$2:$a2),2)=0 "
    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. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Perth, Western Australia, Australia
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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