Hiding Columns by Text Value Working / Hiding Rows by Cell Background Color Not

[Vdoc]

Hello all,

First post so please bear with me. I have some vba code on a sheet that works well to hide columns based on text in a cell (eg. "Test1" hides columns B:P)

Trying to do the same for rows but with background color but not working and have tried so many variations just figured I'd pause and see if I could reach out to the community to help.

Thanks in advance for any ideas offered!

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("Q4"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
       
        Case Is = "Test0":
                  Columns("A:ZZ").EntireColumn.Hidden = False
                            'Rows("5:5").EntireRow.Hidden = False

        Case Is = "Test1":
                            Columns("Q:BH").EntireColumn.Hidden = False
                            Columns("B:P").EntireColumn.Hidden = True

        End Select
End If

If Not Application.Intersect(Range("B7"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Interior.ColorIndex
    Case xlNone, 10:  Rows("5:5").EntireRow.Hidden = True
    Case Else: Rows("5:5").EntireRow.Hidden = False

End Select
End If


End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Are expecting the code to run when you change the colour of B7?
If so, that will not work as change of colour does not trigger the Worksheet_Change event code.

If you change the colour of B7and then also change the value in it, the code should work as I think you are expecting.

BTW, when posting code, please use code tags. See my signature block below for more information. I fixed that in your post for you this time.

 

[Vdoc]

Thanks for the correction will make sure to do going forward. This is the code I had that changes the color.

Maybe it makes more sense to add it to this instead? Basically I have an excel sheet where I double click and background of cell goes black or green.

What I want to code is depending on the combinations that are black or green in the range B7:O7 it will hide certain rows.

Hopefully that makes sense.

Thanks in advance!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.EnableEvents = False
Set Keycells = Range("B7:O7")
Cancel = True
If Not Application.Intersect(Keycells, Range(Target.Address)) _
           Is Nothing Then
Select Case Target.Interior.ColorIndex
    Case xlNone, 10: Target.Interior.ColorIndex = 1
    Case Else: Target.Interior.ColorIndex = 10
End Select
End If

 

[Vdoc]

I had trouble with this code as it didnt seem to result in what I was trying to achieve. Is there a way to tell it if I double click on B7 to turn it to black but C7 is green that it doesn't hide the row?

Basically had trouble including multiple factors. Eg. B7 or C7, B7 and C7 and the row displays.

 

[Peter_SSs]

Maybe it makes more sense to add it to this instead?

Yes, I think that would make good sense.

What I want to code is depending on the combinations that are black or green in the range B7:O7 it will hide certain rows.

Is there a way to tell it if I double click on B7 to turn it to black but C7 is green that it doesn't hide the row?

I didn't understand exactly what you mean by these two statements. Can you spell it out in more detail? Perhaps with a few specific examples?

 

[Vdoc]

Yes basically either B7 and or C7 when doubleclicked green should reveal a column. I tried to add to the code above but was unsuccessful as it only seemed to react cell by cell. Meaning I couldnt find a way to have it look to see if the background on either b7 or c7 was green before hiding. So I would double click to make B7 black and it would hide the row even though C7 was green.

Hopefully that adds clarity.

 

[Vdoc]

Yes basically either B7 and or C7 when doubleclicked green should reveal a column. I tried to add to the code above but was unsuccessful as it only seemed to react cell by cell. Meaning I couldnt find a way to have it look to see if the background on either b7 or c7 was green before hiding. So I would double click to make B7 black and it would hide the row even though C7 was green.

*Should reveal a row

 

[Peter_SSs]

either B7 and or C7 when doubleclicked green ...

Is it already green when double clicked? Or is the double-click turning it green?

... should reveal a column.

Which column?

And what does black have to do with it?

Further, your original code referred to the range "B7:O7". Is D7:O7 still relevant and if so how?

 

[Vdoc]

Is it already green when double clicked? Or is the double-click turning it green?

Which column?

And what does black have to do with it?

Further, your original code referred to the range "B7:O7". Is D7:O7 still relevant and if so how?

Thanks for your patience with me.

Q1: Sorry I meant row not column: (In the first code but not yet included in the second was:

Rows("5:5").EntireRow.Hidden = True

)
Q2: Black just means not selected. So basically if all are black all rows are hidden (for simplicity in my example only row5 will be hidden)
Q3: B7:07 is still relevant - combinations of whether they are active (green) or inactive (black) determines which rows are hidden.

 

[Vdoc]

Through your help so far I think I have discovered the actual question I was trying to ask.

I get a type mismatch error with the following but trying to say if either two cells B7,C7 are green (10) then row 5 is shown.
If black (1) then row is hidden. Hopefully my attempt at the code can help supplement any confusion my description of the issue has caused.

Select Case Target.Interior.ColorIndex
    Case Range("B7","C7") = 10:
    Rows("5:5").EntireRow.Hidden = False
    Case Is = 1:
    Rows("5:5").EntireRow.Hidden = True