Hide rows based on colour
 

I just want to know, is there a way to write a macro that will;

"hide rows base dupon a certain colour".

I know there are add-ins that enable sorting etc by colour but i just need to
hide??

Anything back would be most appreciated as i am struggling!!

Many thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200701/1

Hide rows based on colour
 

Here's an example that will hide and unhide rows based on the
background colour of Column A:


Public ToggleHide As Boolean

Sub HideReds()

Dim EmptyRow As Boolean, MyRow As Integer

If ToggleHide = True Then
ToggleHide = False
Else
ToggleHide = True
End If

MyRow = 0

Do While EmptyRow = False
MyRow = MyRow + 1
With ActiveSheet.Cells(MyRow, 1)
If Len(.Value) 0 Then
If .Interior.Color = 255 Then
.EntireRow.Hidden = ToggleHide
End If
Else
EmptyRow = True
Exit Do

End If
End With
Loop

End Sub


You can change which column it looks at by changing this line:

With ActiveSheet.Cells(MyRow, 1)

And you can change it to look at the font instead of the background
colour by changing this line:

If .Interior.Color = 255 Then

to

If .Font.Color = 255 Then

255 is the colour red, which you can also change. If you're not sure
of what number to use, you can interrogate a cell whose background
colour you have changed by typing the following in your Immediate
Window in VBA:

?Range("A1").interior.color

replacing "A1" with the position of your cell.

Hope this helps!

Tristan



Ozzie via OfficeKB.com wrote:

I just want to know, is there a way to write a macro that will;

"hide rows base dupon a certain colour".

I know there are add-ins that enable sorting etc by colour but i just need to
hide??

Anything back would be most appreciated as i am struggling!!

Many thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200701/1

Hide rows based on colour
 

Tristan,

That was a big help, many thanks for your response,

Cheers


wrote:
Here's an example that will hide and unhide rows based on the
background colour of Column A:

Public ToggleHide As Boolean

Sub HideReds()

Dim EmptyRow As Boolean, MyRow As Integer

If ToggleHide = True Then
ToggleHide = False
Else
ToggleHide = True
End If

MyRow = 0

Do While EmptyRow = False
MyRow = MyRow + 1
With ActiveSheet.Cells(MyRow, 1)
If Len(.Value) 0 Then
If .Interior.Color = 255 Then
.EntireRow.Hidden = ToggleHide
End If
Else
EmptyRow = True
Exit Do

End If
End With
Loop

End Sub

You can change which column it looks at by changing this line:

With ActiveSheet.Cells(MyRow, 1)

And you can change it to look at the font instead of the background
colour by changing this line:

If .Interior.Color = 255 Then

to

If .Font.Color = 255 Then

255 is the colour red, which you can also change. If you're not sure
of what number to use, you can interrogate a cell whose background
colour you have changed by typing the following in your Immediate
Window in VBA:

?Range("A1").interior.color

replacing "A1" with the position of your cell.

Hope this helps!

Tristan

I just want to know, is there a way to write a macro that will;

[quoted text clipped - 10 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200701/1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200701/1

Hide rows based on colour
 

Hi Ozzie

EasyFilter can hide rows also
http://www.rondebruin.nl/easyfilter.htm


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ozzie via OfficeKB.com" <u18021@uwe wrote in message news:6c82937e7f834@uwe...
I just want to know, is there a way to write a macro that will;

"hide rows base dupon a certain colour".

I know there are add-ins that enable sorting etc by colour but i just need to
hide??

Anything back would be most appreciated as i am struggling!!

Many thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200701/1