Formatting cells

[Lavanya Pandian]

I have a drop down list and depending its selection, the headings of a table
changes. If selection A has 5 columns for the table, Selection B has 7
columns for that table. I want to draw borders to that table, which also
alters depending on the selection. How can i do it?

 

[Carim]

Hi,

I am afraid it is only possible with VBA ... with an event macro ...
Private Sub Worksheet_Change(ByVal Target As Range)

Are you familiar with this issue ...?

 

[Lavanya Pandian]

Nope. Can u suggest a site, where i can read & learn more on this?

 

[Carim]

I would suggest you first describe very precisely your problem ...
by indicating how your worksheet is structured, where is the table,
etc...

 

[Lavanya Pandian]

I have to select an option form a drop down list from B3, then the various
headings of the table appears below, say from column D3 to D15. The first two
columns of the table will remain same (ID and Description) for any selection.
If the selection from the list is X, then i might have headings from D3 to
D15, if the selction is Y, i might have headings from D3 to D10. I have about
7 items in my selection list. all the information is in the same sheet.

I want to draw borders to this table. Am i being clear?

 

[Carim]

Here is an example you can adapt to your specific situation :

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
Dim i As Integer
Set Target = Range("b3")
If Target Is Nothing Then Exit Sub
'Clean whole sheet
Cells.Select
With Selection
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.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
Range("B3").Select
'Adjusts to table
Select Case Target.Value
Case Is = "A"
i = 5
Case Is = "B"
i = 7
Case Is = "C"
i = 9
Case Is = "D"
i = 11
Case Is = "E"
i = 12
Case Is = "F"
i = 14
Case Is = "G"
i = 16
End Select

'Adjusts the table size
Range("D2:E" & i).Select

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Range("B3").Select

Application.ScreenUpdating = True
End Sub


HTH