Hide or Unhide certain columns based on a cell value

  • Thread starter Thread starter gonecrazybacksoon
  • Start date Start date
G

gonecrazybacksoon

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.
 
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

HTH
 
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
columns.
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.

Sachin
 
Back
Top