Hide Rows based on cell color

**jeff.p** · 11-26-2007, 01:47 PM

Hello

I've created a named Range ("VRange") in column V16:V600

Within this range I have numerous cells with the color Red.

I would like to create a button which hides all the rows, within this range, that are non-Red color (Leaving only the Red showing)

Is there a way to create a macro without using conditional formatting?

Thanks for any help

Jeff

**Simon Lloyd** · 11-26-2007, 01:58 PM

Try this:

Sub hide_rows()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("vrange")
For Each MyCell In Rng
If MyCell.Interior.ColorIndex = xlNone Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

**jeff.p** · 11-26-2007, 03:33 PM

Outstanding..

Thanks, Simon!

May ask a follow up question?

If the "VRange" contained various colored cells, but I only wanted to show the green colored ones how could your code be modified to accommodate this...?

thanks again!

Jeff

**VBA Noob** · 11-26-2007, 04:45 PM

Try the below but change 4 to your green shade. Use the VBA recorder to found the color index of your shade of green

Sub hide_rows()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("vrange")
     For Each MyCell In Rng
          If Not MyCell.Interior.ColorIndex = 4 Then
               MyCell.EntireRow.Hidden = True
         End If
     Next MyCell
End Sub

VBA Noob