Writing a macro to hide columns based on cell value

J

JAbels001

I'm trying to write a macro that will automatically hide certain columns
based on a cell's value. Basically, if C3 = "Sales" I need columns R, S, and
T to be hiden; however if C3 does not equal "Sales" i need columns L, M and N
to be hiden, but R, S, and T need to come back into view and vice versa. Is
there a macro that can assist me? Please help. Thanks!
 
L

Luke M

Right click on sheet tab, view code, and paste appropriate macro in.
If C3 is controlled by formula:

Private Sub Worksheet_Calculate()

If Range("c3").Value = "Sales" Then
Columns("R:T").EntireColumn.Hidden = True
Columns("L:N").EntireColumn.Hidden = False
Else
Columns("R:T").EntireColumn.Hidden = False
Columns("L:N").EntireColumn.Hidden = True
End If

End Sub


If inputted manually:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("C3") Then
If Range("c3").Value = "Sales" Then
Columns("R:T").EntireColumn.Hidden = True
Columns("L:N").EntireColumn.Hidden = False
Else
Columns("R:T").EntireColumn.Hidden = False
Columns("L:N").EntireColumn.Hidden = True
End If
End If

End Sub
 
J

JAbels001

Worked like a charm! Thanks!

Luke M said:
Right click on sheet tab, view code, and paste appropriate macro in.
If C3 is controlled by formula:

Private Sub Worksheet_Calculate()

If Range("c3").Value = "Sales" Then
Columns("R:T").EntireColumn.Hidden = True
Columns("L:N").EntireColumn.Hidden = False
Else
Columns("R:T").EntireColumn.Hidden = False
Columns("L:N").EntireColumn.Hidden = True
End If

End Sub


If inputted manually:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("C3") Then
If Range("c3").Value = "Sales" Then
Columns("R:T").EntireColumn.Hidden = True
Columns("L:N").EntireColumn.Hidden = False
Else
Columns("R:T").EntireColumn.Hidden = False
Columns("L:N").EntireColumn.Hidden = True
End If
End If

End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top