VBA to automatically unhide columns based on a specific dropdown selection

[AshKate]

Hello!

I am in need of a VBA that automatically runs if a user on my worksheet chooses a specific dropdown value.

I would like Columns X and Y to automatically become visible only if a color code "Black" is selected from the dropdown in Column W. With all other values (Green, Blue, Purple, Orange, Yellow, Red) Columns X and Y can remain hidden as the information isn't relevant then.

I would greatly appreciate the help. I am slightly new to VBA and have spent hours looking for what I need but cannot seem to piece it all together.

Thank you,

Ashley

 

[Michael M]

Which cell is the dropdown in ??

 

[AshKate]

The dropdown is in column W starting in W4

 

[Michael M]

Maybe this pasted into the sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("W4")) Is Nothing Then Exit Sub
If Target.Value = "Black" Then
    Range("X:Y").EntireColumn.Hidden = False
Else
    Range("X:Y").EntireColumn.Hidden = True
    Range("A19").Value = ""
End If
End Sub

 

[AshKate]

Thank you for taking the time to help me! I truly appreciate it.

I placed it in the sheet code. This works beautifully if "Black" is in W4 (as you have it set for the range). However, I need it run whenever "Black" is selected in any part of the column W. Could I make the range W4 through the end of the column?

 

[Michael M]

I can, but what if Black is in W4 and Red is in W5, then Black in W6.......etc, etc !!!!

 

[AshKate]

I don't think I understand your concern. Let me share some of my project with you and that might clear it up. I am working on a bill of materials. Each row has a different part and those parts have chemicals that are rated for their toxicity (the colors are the ratings). It's important for my users to know if there's even one "black" toxicity rating out of all the parts that make a product. So it doesn't matter how many times it's present, it only matters that it is present at all. So if there's 50 "black" ratings or 1, I need the X and Y columns unhidden. I thought that if a black was selected, two columns (x and y) could be made visible to further show important information about the part with a "black" rating. Does this make sense?

 

[AshKate]

The part(s) that could have a "black" rating could be in any row that's why I need it to have all of column W as the range.

 

[Michael M]

Okay...this assumes there is data in Column "A" as part of your project

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
If Intersect(Target, Range("W4:W" & lr)) Is Nothing Then Exit Sub
If WorksheetFunction.CountIf(Range("W4:W" & lr), "Black") >= 1 Then
    Range("X:Y").EntireColumn.Hidden = False
Else
    Range("X:Y").EntireColumn.Hidden = True
End If
End Sub

 

[AshKate]

WOW. Wonderful! Thank you very much.