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

Vdoc

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!



VBA Code:
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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!



VBA Code:
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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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

New Member
Joined
Dec 30, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,370
Messages
5,624,290
Members
416,018
Latest member
mirceaon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top