Show/Hide column based on Dropdown selection in Ex

Question

I am trying to show a hidden column based on an option of my dropdown. For a single row it works fine but when I want to extend my Range for 10 rows

If Range("$CF$5: $CF$15") = "Others" Then

Tt displays a Runtime error 13.

Below is my code. Thanks for helping me out.

If Range("$CF$5") = "Others" Then
    ActiveSheet.Columns("CG").EntireColumn.Hidden = False
Else
    ActiveSheet.Columns("CG").EntireColumn.Hidden = True
End If 

Answer 1

How about this? This assumes that if a single cell in the range is set to "Others" that the CG column will be shown, and if none them are, CG will be hidden. Not sure if that's what you're really after?

Dim cell As Range

For Each cell In Range("$CF$5:$CF$15")

    If cell = "Others" Then
        ActiveSheet.Columns("CG").EntireColumn.Hidden = False
        Exit For
    Else
        ActiveSheet.Columns("CG").EntireColumn.Hidden = True
    End If

Next cell

Answer 2

You can't compare the value in the range like you are doing it.

If Range("$CF$5: $CF$15") = "Others"

There are many ways to do the comparison. Looping through the range is the most common way. Below is a another way to check if all the cells in a vertical range have the same value.

Is this what you are trying?

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Set your worksheet here
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Set your range here
        Set rng = .Range("CF5:CF15")

        '~~> Check if any cell in the range have "Others"
        If Application.WorksheetFunction.CountIf(rng, "Others") = _
        rng.Rows.Count Then
            .Columns("CG").EntireColumn.Hidden = False
        Else
            .Columns("CG").EntireColumn.Hidden = True
        End If
    End With
End Sub

EDIT:

And if you want to Show/Hide the column even if there is one instance of "Others` then also you don't need a loop. See this

Sub Sample()
    Dim ws As Worksheet
    Dim rng As Range

    '~~> Set your worksheet here
    Set ws = ThisWorkbook.Sheets("Sheet1")

    With ws
        '~~> Set your range here
        Set rng = .Range("CF5:CF15")

        '~~> Check if all the cells in the range have "Others"
        If Application.WorksheetFunction.CountIf(rng, "Others") > 0 Then
            .Columns("CG").EntireColumn.Hidden = False
        Else
            .Columns("CG").EntireColumn.Hidden = True
        End If
    End With
End Sub