Cascading color commands (code included)

[TXNCPO]

Good Afternoon,

I found help here (many times)

Currently I have a Spreadsheet that will expand (unhide rows) based on a color selection in column "a". That part work GREAT (see lines 3-7) of code below. I am attempting to see if i can add a layer (for future requests). My attempt (one of them) to hide row B12 based on selection in B11 does not work as expected. to be fair I did not think it would, I assume the first part of the sub is cancelling out what I would like to do with the B11/B12.

The code is longer, but repetitive. (does same action for each set of 7 rows)


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("$A10:A100")) Is Nothing Then


If Range("A10").Value = "GREEN" Then
Rows("11:16").EntireRow.Hidden = True
Else
Rows("11:16").EntireRow.Hidden = False

End If


If Range("B11").Value = "GREEN" Then
Rows("12").EntireRow.Hidden = True
Else
Rows("12").EntireRow.Hidden = False
End If

End Sub

 

[Yongle]

What is supposed to happen ...

When both A10 and B11 are "GREEN" ?
When only A10 is "GREEN" ?
When only B11 is "GREEN" ?

Is any change in A10:A100 the trigger for hiding\ unhiding each set of 7 rows ?

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/hide-unhide-based-on-selection-multi-layer.41637/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[TXNCPO]

I will try to clarify,

When the user is going thru the workbook, If it is determined by reading the rational in D10 (an area on spreadsheet that only provides a written explanation of what a CM plan is) that there is a CM plan in place, A10 would stay green and the user would never see/use the "sub-elements" in B11, B13, B15. If the user selects A10 to yellow or red, rows B11, B13, and B15 would "unhide" with B12, B14, and B16 hidden unless the user selects yellow or red in B11, B13, or B15.

hope this helps.

 

[TXNCPO]

Fluff,

My apologies, I did not realize the sites were linked. I knew I was going to be out of office yesterday and was only trying to get the question out there. won't happen again.

V/R Joe

 

[TXNCPO]

RESOLVED,

Yongle, Thank you, Thank you.

I had my 3rd cup of coffee and re-read your initial reply. and it jumped off the screen "Is any change in A10:A100 the trigger for hiding\ unhiding each set of 7 rows ?" I was not "telling" the worksheet to look in column B at all for the additional layers. (sorry about bad typing, no sleep yet)

Once i added the reference to B column it works as intended. separate but equal to column A


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Range("$A10:A100, $B10:B100")) Is Nothing Then


If Range("A10").Value = "GREEN" Then
Rows("11:16").EntireRow.Hidden = True
Else
Rows("11:16").EntireRow.Hidden = False

End If




If Range("B11").Value = "GREEN" Then
Rows("12").EntireRow.Hidden = True
Else
Rows("12").EntireRow.Hidden = False
End If


If Range("B13").Value = "GREEN" Then
Rows("14").EntireRow.Hidden = True
Else
Rows("14").EntireRow.Hidden = False
End If



If Range("B15").Value = "GREEN" Then
Rows("16").EntireRow.Hidden = True
Else
Rows("16").EntireRow.Hidden = False
End If

 

[Yongle]

Glad you sussed it!