hide and unhide columns based on dropdown selection

[rup1234]

I have a dropdown in column B and i want to hide and unhide column based on dropdown selection .

For example

B4 - is my dropdown list and it contains numbers like

1
2
12
14
3

now based on condition if user selects less than 12 then it hides the H and I columns and if user selects greater than 12 then it unhides the H and I columns.

 

[Ramballah]

I have a dropdown in column B and i want to hide and unhide column based on dropdown selection .

For example

B4 - is my dropdown list and it contains numbers like

1
2
12
14
3

now based on condition if user selects less than 12 then it hides the H and I columns and if user selects greater than 12 then it unhides the H and I columns.

Is this only for H and I and those numbers?

 

[rup1234]

only for H and I

 

[Yongle]

This answers your question but see my next post as well
You have not said what happen if B4 = 12, so you may need to adjust this

Goes in SHEET module (right-click on sheet tab \ View Code \ paste code in that window \ {Alt}{F11} to go back to Excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B4" Then
        With Me
            If Target < 12 Then .Range("H:I").Columns.Hidden = True Else .Range("H:I").Columns.Hidden = False
        End With
    End If
End Sub

 

[Yongle]

If you want to use different values in B4 to hide other columns as well then I would use a different approach
- I would not use <
- instead I would unhide ALL columns first and then hide the columns to be hidden for each value of B4
- a separate string is required for each possible value in B4
- note how the column string is constructed

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim colStr As String
    If Target.Address(0, 0) = "B4" Then
        With Me
        'show all columns
            .Cells.Columns.Hidden = False
        'hide the ones that should be hidden based on value in cell
            Select Case Target
                Case 1:     colStr = "F:G,K:K,M:M"
                Case 2:     colStr = "Q:Q"
                Case 14:    colStr = "Z:Z"
                'etc
                Case Else:  Exit Sub
            End Select
            .Range(colStr).Columns.Hidden = True
        End With
    End If
End Sub

The hidden columns are for illustration only to show you how to build the strings for different scenarios and ignore the columns mentioned in your question

 

[rup1234]

This answers your question but see my next post as well
You have not said what happen if B4 = 12, so you may need to adjust this

Goes in SHEET module (right-click on sheet tab \ View Code \ paste code in that window \ {Alt}{F11} to go back to Excel)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "B4" Then
        With Me
            If Target < 12 Then .Range("H:I").Columns.Hidden = True Else .Range("H:I").Columns.Hidden = False
        End With
    End If
End Sub

how can i hide only cell value like H4 in that

 

[Ramballah]

how can i hide only cell value like H4 in that

You can't hide single cells.

 

[rup1234]

You can't hide single cells.

Is their any formula to hide single cell

 

[Ramballah]

Is their any formula to hide single cell

No, all i can think of is deleting the cell's contents and placing it somewhere else temporarily

 

[Yongle]

You said originally that you wanted to hide\unhide columns - but now you are saying you want to hide individual cells

(As already explained above) complete rows and columns can be hidden BUT individual cells cannot be hidden. So to hide a cell, hide either its column or its row.

There are various ways to hide the value of a cell

One way - make the font colour identical to cell background ...

With ActiveCell
    .Font.Color = .Interior.Color
End With

To toggle between default font colour and cell background colour ....

With ActiveCell
    Select Case .Font.Color
        Case 0:         .Font.Color = .Interior.Color
        Case Else:      .Font.Color = 0
    End Select
End With

If the above does not do what you want, please explain what you are trying to achieve