Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell format for hidden rows (Excel2003)

    Often I find out the hard way that someone has hidden rows or columns. I normally do not run through the column alphabet or count rows, of course. I wish they would put a bright line between hidden rows or columns headers to viusually signal that fact, but in the meatime, what would be the best way to do this: when there is a hidden row or column adjacent to a cell, change the cell border to some color such as orange or bright blue on that side of the cell. Could conditional formatting do that or would a macro be more efficient? Is there a property one would look for? If the row or column header area could show it without wasting all that effort to change the cell borders, that would be even better. Thanks for any ideas

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format for hidden rows (Excel2003)

    The VBA code below will do what you asked:

    <code>
    Public Sub FlagHidden()
    Dim oUsed As Range
    Dim lRowMax As Long, lColMax As Long, I As Long
    Set oUsed = ActiveSheet.UsedRange
    lRowMax = oUsed.Offset(oUsed.Rows.Count, 0).Row - 1
    lColMax = oUsed.Offset(0, oUsed.Columns.Count).Column - 1
    For I = lColMax To 1 Step -1
    If Not ActiveSheet.Range("A1").Offset(0, I).EntireColumn.Hidden Then
    If ActiveSheet.Range("A1").Offset(0, I - 1).EntireColumn.Hidden Then
    With ActiveSheet.Range("A1").Offset(0, I).EntireColumn.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 5
    End With
    End If
    End If
    Next I
    For I = lRowMax To 1 Step -1
    If Not ActiveSheet.Range("A1").Offset(I, 0).EntireRow.Hidden Then
    If ActiveSheet.Range("A1").Offset(I - 1, 0).EntireRow.Hidden Then
    With ActiveSheet.Range("A1").Offset(I, 0).EntireRow.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlThin
    .ColorIndex = 5
    End With
    End If
    End If
    Next I
    End Sub
    </code>
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell format for hidden rows (Excel2003)

    That's great...that will save me lots of trouble! I will study it and learn something. Thanks much.

Posting Permissions

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