Excel - Hide Red Rows

[poison1701]

I would like to have a toggle button on a spreadsheet that is able to hide/unhide a row if any cell in that row has a background color of red.

Using code I have found, I came up with the following, but it will only hide a row if it's empty.

Sub HideRedRows(Mode As Boolean)
Dim r As Range
Dim i As Long
Set r = ActiveSheet.UsedRange.Rows(1)
For i = 1 To ActiveSheet.UsedRange.Rows.Count
If WorksheetFunction.CountA(r) = 0 Then
Set r = r.Offset(1, 0)
r.Offset(-1, 0).EntireRow.Hidden = Mode
Else
Set r = r.Offset(1, 0)
End If
Next i
Set r = Nothing
End Sub
Private Sub ToggleButton1_Click()

Select Case ToggleButton1.Value

Case True
HideRedRows (ToggleButton1.Value)
ToggleButton1.Caption = "Unhide Rows"

Case False
HideRedRows (ToggleButton1.Value)
ToggleButton1.Caption = "Hide Rows"

End Select

End Sub

 

[DrBowes]

How about this:

Sub hd()
Dim rge As Range
Set rge = ActiveSheet.UsedRange
For r = 1 To rge.Rows.Count
For c = 1 To rge.Columns.Count
If rge(r, c).Interior.ColorIndex = 3 Then
Rows(r).Hidden = True
Exit For
End If
Next c
Next r
Set rge = Nothing
End Sub

Sub unhd()
Dim rge As Range
Set rge = ActiveSheet.UsedRange
rge.Rows.Hidden = False
Set rge = Nothing
End Sub

 

[xlbo]

DrBowes code should work but not if the cells are coloured by conditional formatting as this doesn't set the interior.colorindex property.

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[poison1701]

The color will be set by conditional formatting. What can I do in this case?

 

[DrBowes]

Whatever condition is required to make the cell red should be substituted for the test of interior colorindex in the IF statement.

e.g: if cells(r,c).value > 10 then .... if all cells containing values over 10 are made red by conditional formatting.

(This the real reason for hiding the cells, not the fact that they are red.)

This could probably also be done using the FormatConditions property but may as well cut out the middle man.

 

[poison1701]

I am preferring to test if conditional formatting is applied, so that the end user of the spreadsheet can easily make changes to the format and still end up with the same result. How can I test if conditional formatting is applied to a cell? *Note I dont know if it matters but we are using Excel 2002.

 

[xlbo]

AFAIK, you can only tell if conditional formatting has been APPLIED not whether it has been ACTIVATED. You will need to do as drBowes advises and use the same test in code as you have done on for the conditional formatting

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[poison1701]

Here is a link to the answer I was seeking. Thanks to all who helped.

http://www.cpearson.com/excel/CFColors.htm

 

[xlbo]

Seems like a lot of code - are your conditions really that complex that they'd take more code than that link ??

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244

 

[poison1701]

Definitely not, however, in order to make it flexible for the end user, this will work just fine, and it runs fast.

 

[xlbo]

Fair enough - glad you got it sorted

Rgds, Geoff
Quantum materiae materietur marmota monax si marmota monax materiam possit materiari?
Want the best answers to your questions ? faq222-2244