selection change issue

[bhandari]

Here is the image with Brief Description
if E3 value =1
Format need to draw with pocket1 as shown in picture
if E3 value =2
Format need to draw with pocket1 and Pocket 2 should be visible

https://drive.google.com/open?id=1IRsWrgdi0kD6fbu6W3PRCrRdY95ihaUw

 

[jolivanes]

It deletes Range H5:K16 to the left. If that needs changing, change it or let us know what it needs to be.

Sub Maybe()
Application.ScreenUpdating = False
Select Case Range("E3").Value

Case 1
Range("H5:K16").Delete Shift:=xlToLeft
Range("H5").Value = "Pocket 1"
With Range("H5:H16")
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With

Case 2
Range("H5:K16").Delete Shift:=xlToLeft
Range("H5:I5").Value = Array("Pocket 1", "Pocket 2")
With Range("H5:I16")
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
End With

Case 3
Range("H5:K16").Delete Shift:=xlToLeft
Range("H5:J5").Value = Array("Pocket 1", "Pocket 2", "Pocket 3")
With Range("H5:J16")
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
End With

Case 4
Range("H5:K16").Delete Shift:=xlToLeft
Range("H5:K5").Value = Array("Pocket 1", "Pocket 2", "Pocket 3", "Pocket 4")
With Range("H5:K16")
    .Borders(xlEdgeLeft).LineStyle = xlContinuous
    .Borders(xlEdgeRight).LineStyle = xlContinuous
    .Borders(xlEdgeTop).LineStyle = xlContinuous
    .Borders(xlEdgeBottom).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinuous
    .Borders(xlInsideVertical).LineStyle = xlContinuous
End With
End Select
Application.ScreenUpdating = True
End Sub

 

[jolivanes]

If deleting is not an option.
Under every line where it has Case (1, 2, 3, 4), replace this line

Range("H5:K16").Delete Shift:=xlToLeft

With this

With Range("H5:K16")
    .ClearContents
    .Borders.LineStyle = xlNone
End With

 

[bhandari]

its not triggering if i run your code its simply coming whole format which i have shown in picture

can u make it for event trigger..i want to add it in event change

based on value E3

format need to tigger

E3=1

H5:H16 format should be visible

 

[jolivanes]

Maybe, after 239 posts, you could find out how to do that by searching the net or this site.
If you really can't find anything on the net, let us know.

Oh well, it is the holiday season after all.
Put this in the sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$3" Then
        Call Maybe_B
    End If
End Sub

Have a Happy New Year.

 

[Rick Rothstein]

It deletes Range H5:K16 to the left. If that needs changing, change it or let us know what it needs to be.

This is basically your code with the Select Case removed...

[table="width: 500"]
[tr]
	[td]Sub Maybe2()
  Dim Target As Range
  Set Target = Range("E3")
  With Range("H5:K16")
    .ClearContents
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeTop).LineStyle = xlNone
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
  End With
  If Target.Value Like "[1-4]" Then
    With Range("H5:H16").Resize(, Target.Value)
      .Rows(1) = Split(Left("Pocket1 Pocket2 Pocket3 Pocket4", 8 * Target.Value))
      .Borders(xlEdgeLeft).LineStyle = xlContinuous
      .Borders(xlEdgeRight).LineStyle = xlContinuous
      .Borders(xlEdgeTop).LineStyle = xlContinuous
      .Borders(xlEdgeBottom).LineStyle = xlContinuous
      .Borders(xlInsideHorizontal).LineStyle = xlContinuous
      .Borders(xlInsideVertical).LineStyle = xlContinuous
    End With
  End If
End Sub[/td]
[/tr]
[/table]

 

[bhandari]

Thank you for your help ..i got it