1

I have a workbook set up to where Sheet1 is editable due dates in multiple columns (columns C through T) for certain listed names in columns A&B; Rows 1&2 are a header so data input starts on row 3.

Sheet2 is identical using INDIRECT formulas as a protected page with conditional formatting that highlights certain cells either red or yellow if the due date is coming up.

I'm inexperienced with VBA and have been searching for a macro that meets the following criteria:

On Sheet2 only, if the row does not contain any cells that are red or yellow then hide those non-colored rows.

Any help would be greatly appreciated. I have only found code on hiding rows based on criteria in single columns.

pnuts
  • 58,317
  • 11
  • 87
  • 139
user2214690
  • 35
  • 1
  • 2
  • 6
  • Try checking this.. It's hard of your question..http://www.mrexcel.com/forum/excel-questions/84136-need-visual-basic-applications-hide-rows-have-yellow-fill.html – Kasnady Mar 27 '13 at 08:33

2 Answers2

4

Here's a little script to get you started. It will loop through each column of each row and check the color of each cell. If any color is found, that row will be skipped. If there are no cells found that have any color, the row will be hidden. In other words, all fully white row will be hidden.

CODE:

Public Sub HideUncoloredRows()
    Dim startColumn As Integer
    Dim startRow As Integer

    Dim totalRows As Integer
    Dim totalColumns As Integer

    Dim currentColumn As Integer
    Dim currentRow As Integer

    Dim shouldHideRow As Integer

    startColumn = 1     'column A
    startRow = 1        'row 1
    totalRows = Sheet2.Cells(Rows.Count, startColumn).End(xlUp).Row

    For currentRow = totalRows To startRow Step -1
        shouldHideRow = True
        totalColumns = Sheet2.Cells(currentRow, Columns.Count).End(xlToLeft).Column
        'for each column in the current row, check the cell color
        For currentColumn = startColumn To totalColumns
            'if any colored cell is found, don't hide the row and move on to next row
            If Not Sheet2.Cells(currentRow, currentColumn).Interior.ColorIndex = -4142 Then
                shouldHideRow = False
                Exit For
            End If
        Next

        If shouldHideRow Then
            'drop into here if all cells in a row were white
            Sheet2.Cells(currentRow, currentColumn).EntireRow.Hidden = True
        End If
    Next
End Sub

BEFORE

enter image description here

AFTER

enter image description here

StoriKnow
  • 5,738
  • 6
  • 37
  • 46
  • I apologize if my wording was a little hard to understand. What I need is the opposite. I need the colored rows to stay. The white ones must be hidden. – user2214690 Mar 27 '13 at 16:23
  • I probably just misread :) the correct change has been made to the original answer, and now should do as you ask. – StoriKnow Mar 27 '13 at 17:00
  • 1
    Thanks! Problems: If there is no data input in the A column, then the macro does not run in that row. Further, if there is no data input in the colored cell -- the macro will hide it. This macro also requires me to manually click it to run. Is there a way to make it auto-run on start? Is there a way for this to be dynamic? IE: Since this sheet is linked to another, if data on the other sheet forces a cell to change colors on Sheet2 -- can it auto-unhide? – user2214690 Mar 27 '13 at 17:48
  • 1
    Hmm... Also just ran into another issue. Apparently this macro only detects cells that are colored manually. If they are conditionally formatted to be highlighted, it treats the colored cells as if they were non-colored. If it is too much of a hassle, I understand. Thanks for the help so far! – user2214690 Mar 27 '13 at 17:56
  • 1
    The above code/example was meant as a starting point to get you going, not as a full fledged working solution. I suggest you take a look into the code and play with it a bit, figure out how it works by doing some research, then posting more questions here about what you've tried and where you're stuck. To make it autorun on start you can look at placing the code in the `Workbook_Open` event. To have it run when changes are made to `Sheet2`, you can look at the `Worksheet_Change` event. – StoriKnow Mar 27 '13 at 18:48
  • Thanks. I was able to play with it enough to find the desired results. Reading your code helped me figure out what to mess with. I ended up just changing it to a command button to hide/sort as desired based on date value (since the conditional formatting was interfering). Private Sub CommandButton1_Click() CommandButton1.Caption = "Sort" Rows("3:65").Select Selection.EntireRow.Hidden = True For Each cell In Range("C3:T65") If cell.Value <= Date + 60 And cell.Value > 0 Then cell.EntireRow.Hidden = False End If Next End Sub – user2214690 Mar 27 '13 at 19:18
  • I'm glad you were able to get things working as you envisioned. Glad to help! Best of luck with the rest of your project. – StoriKnow Mar 27 '13 at 20:02
0
row = 1    
do
  flag = false
  col = 1
  do
    .cells(row,col).select
    if selection.interior.colorindex <> vbWhite then flag = true
  loop until (col = lastcol) or (flag = true)
  if flag then 
    rows(row).delete
    row = row - 1
  end if
  row = row + 1
loop until row = lastrow
g8r
  • 1
  • 2