Excel: Hiding cells that are not colored

Question

I have a script that changes the cell color and a script to hide the cells that are not colored. The hide script works, but it hides ALL the cells, even the colored ones. I noticed that when I use the script that changes the cell color, it does not detect the changes in the excel interface(in the 'Fill Color' settings in 'Home' tab, under the 'font size' selection). I also noticed that when try to change the color of the cells (using the excel interface) that are colored from using the script, it does not change (the color seems to be fixed to whatever is set from the script).

Therefore, it seems like the interface does not detect the changes that are being made using the coloring script.

Also, I noticed the script below takes a while to check/hide all the cells. If there is a way to speed up the process, that would be great!

Any help will be greatly appreciated!

Thank you!

The script to hide uncolored cells:

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 = Sheet1.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 Sheet1.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

The script that changes the color certain cells:

   Range("A8").Select
Application.CutCopyMode = False
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
    "=COUNTIF(Name_Preps,A8)=1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
    .Bold = True
    .Italic = False
    .TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorAccent3 'Changes the cell to green
    .TintAndShade = 0.4
End With
Selection.FormatConditions(1).StopIfTrue = False
End Sub

score 0 · Answer 1 · answered Mar 11 '17 at 04:10

Try to change your condition to follow

    For currentColumn = startColumn To totalColumns
        'if any colored cell is found, don't hide the row and move on to next row
        If Sheet1.Cells(currentRow, currentColumn).Interior.ThemeColor = xlThemeColorAccent3 Then
            shouldHideRow = False
            Exit For
        End If
    Next

score 0 · Answer 2 · edited May 23 '17 at 12:17

conditional formatting is not detected by Interior.ColorIndex and the likes

if you want to go on that way you can see here or here for relevant code

but I'd abandon conditional formatting as well as Select/Selection/Activate/ActiveXXX pattern and go simply this way:

Option Explicit

Sub HandleRowsColorAndVisibility()
    Dim iRow As Long

    With Range("A8", Cells(Rows.count, 1).End(xlUp)) '<--| reference cells from A8 down to column A last not empty cell
        ResetRange .Cells '<--| first, bring range formatting and visibility back to a "default" state
        For iRow = .Rows.count To 1 Step -1 '<--| then start looping through range
            If WorksheetFunction.CountIf(Range("Name_Preps"), .Cells(iRow, 1)) = 1 Then '<-- if current cell matches your criteria ...
                FormatRange .Cells(iRow, 1), True, False, 0, xlColorIndexAutomatic, xlThemeColorAccent3, 0.4 '<--| then format it
            Else '<--| otherwise...
                .Rows(iRow).Hidden = True '<--| hide it!
            End If
        Next
    End With
End Sub

Sub ResetRange(rng As Range)
    rng.EntireRow.Hidden = False
    FormatRange rng, False, False, 0, xlColorIndexAutomatic, -4142, 0
End Sub

Sub FormatRange(rng As Range, okBold As Boolean, okItalic As Boolean, myFontTintAndShade As Single, myPatternColorIndex As XlColorIndex, myInteriorThemeColor As Variant, myInteriorTintAndShade As Single)
    With rng
        With .Font
            .Bold = okBold
            .Italic = okItalic
            .TintAndShade = myFontTintAndShade
        End With
        With .Interior
            .PatternColorIndex = myPatternColorIndex
            .ThemeColor = myInteriorThemeColor
            .TintAndShade = myInteriorTintAndShade
        End With
    End With
End Sub

Excel: Hiding cells that are not colored

2 Answers2