Hide or Unhide certain columns based on a cell value



Hello All

I have a worksheet which calculates payback percentages for Slot
machines, based on how many coins a person plays. Since machines vary
in the amount of coins they are allowed to play, I have the sheet set
up from one coin up to five coins. On the sheet, if they enter the
amount of coins, it should hide the columns that are not needed... eg:
If it is 3 coin machine, it will hide the calculation columns for the
fourth and fifth coin. If, after that, they change the number to 2
coins, it should hide the third coin calculation column, and so on. I
tried achieving this with a Case... Select approach, but I have having
no luck at all.
If anyone has some sort of basic code that hides a column in the same
sheet, based on a numeric input in a cell on that same sheet, please
help. Thank you for your time.

Ardus Petus

With a Worksheet_Change event procedure:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lColumn As Long
If Intersect(Target, Range("A2")) Is Nothing Then Exit Sub
For lColumn = 2 To 5
Columns(lColumn).Hidden = lColumn > Range("A2").Value
Next lColumn
End Sub

See example: http://cjoint.com/?hkljrxXed6



Thanx for the quick response, but the procedure does not do exactly
what I intended.

OK, my target cell is J7... this is where I input either the number 2,
3 or 4 , and depending on what I input, the macro should hide certain
If J7 is 2, then I want to hide columns M to Z.
If J7 is 3, then I want to hide columns R to Z
If J7 is 4, then I want ot hide columns W to Z

Also, If I enter the number 2 in J7, and columns M to Z are hidden, at
a later stage if I enter 3, it should unhide M:p, and so on.

Sorry if I was unclear during my first email.


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