Hide/Unhide rows with specific colour format

[Foxy Lady]

Hi there,
Wonder if any one can help me with this issue,

I have a spreadsheet with data which i need to divide between 3 people. So what I thought I should do is color format every row a different color.
ex. Row 1 = Red
Row 2 = Yellow
Row 3 = Blue
Row 4 = Red
Row 5 = Yellow
Row 6 = Blue etc.
Then add four buttons on the sheet ex. Koos, Piet, Jacob and Unhide All
The idee is when Koos presses his button all Piet and Jocobs rows should be hide and when the Unhide all button is pressed then all the rows should be unhide.

I have found code that will delete the rows with a specific color format and I don't know how to undo it and show all the line that has been delete again.

Hope someone can help

Regards

Foxy Lady

 

[Peter_SSs]

I have found code that will delete the rows with a specific color format and I don't know how to undo it and show all the line that has been delete again.

That code might be a useful starting point. Could you post it?

 

[Foxy Lady]

Hi Peter,

Attached the code
Private Sub CommandButton1_Click()

' Local Variables
Dim rngData As Range
Dim dblCount As Double

Set rngData = Selection
If rngData.Areas.Count > 1 Then Exit Sub

' Delete cells starting at bottom of range
For dblCount = rngData.Rows.Count - 1 To 0 Step -1
If rngData.Range("B1").Offset(dblCount, 0).Interior.ColorIndex = 3 Then rngData.Range("B1").Offset(dblCount, 0).EntireRow.Delete
Next dblCount
For dblCount = rngData.Rows.Count - 1 To 0 Step -1
If rngData.Range("B1").Offset(dblCount, 0).Interior.ColorIndex = 6 Then rngData.Range("B1").Offset(dblCount, 0).EntireRow.Delete
Next dblCount
For dblCount = rngData.Rows.Count - 1 To 0 Step -1
If rngData.Range("B1").Offset(dblCount, 0).Interior.ColorIndex = 5 Then rngData.Range("B1").Offset(dblCount, 0).EntireRow.Delete
Next dblCount
End Sub

This one is a test only, I put all three the color code here but each button should only delete one color format.

If I change the.delete to .hide it gives me a debug error.

I will be waiting for you reply.
Thx a million

Olivia

 

[Foxy Lady]

Is there anyone that can assist me in this regard????

 

[Peter_SSs]

See if this is any use. My assumptions are:

1. The coloured cells are in column B.

2. There is a heading row that will always be visible (and maybe houses the CommandButtons?)

3. Koos is linked to CommandButton1 and colour red (ColorIndex = 3)

4. CommandButton1 has its Caption property initially set to "Show Koos"

This code would save the need for a fourth Command button to "Sow All"

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()<br>    <SPAN style="color:#00007F">Dim</SPAN> rngData <SPAN style="color:#00007F">As</SPAN> Range, rngShow <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>        <SPAN style="color:#00007F">Set</SPAN> rngData = Intersect(.UsedRange, .Columns("B"), .Rows("2:" & .Rows.Count))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rngShow = rngData.Offset(, 1)<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rngData<br>        <SPAN style="color:#00007F">If</SPAN> c.Interior.ColorIndex = 3 <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> rngShow = Union(rngShow, c)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">Set</SPAN> rngShow = Intersect(rngShow, rngData)<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rngShow <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Me.CommandButton1.Caption = "Show Koos" <SPAN style="color:#00007F">Then</SPAN><br>            rngData.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>            rngShow.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            Me.CommandButton1.Caption = "Show All"<br>        <SPAN style="color:#00007F">Else</SPAN><br>            rngData.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>            Me.CommandButton1.Caption = "Show Koos"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>