VBA Program not working to merge cells

[Joek88]

Hi all!

So I am trying to merge cells and unmerge them to the original state they were created as. I am using a VBA code to perform this. Here is the program:

Private Sub sub4(ByVal Target As Range)
If Target.Address = "$D$21" Then ' Assuming drop-down is in cell D21
If IsNumeric(Target.Value) Then
Application.DisplayAlerts = False

If Target.Value = 2 Then
Range("C21:C33").merge
Else
Range("C21:C33").UnMerge
Range("C21:C26").merge
Range("C21:C26").WrapText = False
Range("C21:C26").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C28:C33").Borders(xlEdgeTop).LineStyle = xlContinuous
Range("C28:C33").UnMerge
Range("C28:C33").merge
Range("C28").Value = "-"
Range("C28:C33").Borders(xlEdgeBottom).LineStyle = xlContinuous

If Target.Value = 3 Then
Range("C21:C40").merge
Else
Range("C21:C40").UnMerge
Range("C21:C26").merge
Range("C21:C26").WrapText = False
Range("C21:C26").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C28:C33").merge
Range("C28:C33").WrapText = False
Range("C28:C33").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C35:C40").merge
Range("C35:C40").WrapText = False
Range("C35:C40").Borders(xlEdgeBottom).LineStyle = xlContinuous
Range("C28").Value = "-"
Range("C35").Value = "-"
Range("C28:C33").Borders(xlEdgeBottom).LineStyle = xlContinuous


End If
End If

Application.DisplayAlerts = True
End If
End If
End Sub



Ok so the code is performing this action:


1 is the default setting in the dropdown box

2 will merge both cells together

3 will merge all three cells together

The code works flawless for this action. It is exactly what I want. So I also want to be able to toggle back and forth from each dropdown value and it will revert back to that particular number. If I set it to merge 3 cells but I actually meant to make it 2 cells, I want it to go back to how dropdown 2 looks like. See the problem below:

When I go from 3 to 1 it changes perfect.

THE PROBLEM:

When I go from 3 to 2 it shows absolutely no change at all. Why??? Is the code wrong somewhere??

Thanks for your help!!

 

[Joe4]

I haven't taken the time to fully analyze your code, but see a potential problem with the structure of it.
Note the structure of your IF blocks...

If Target.Value =2 Then
'    do this
Else
'    do this
    If Target.Value =3 Then
'        do this
    Else
'        do this
    End If
End If

The issue is if the value changes to 3, you are going to end up running BOTH the blue and green parts (and you may be having some interference there).

I think a Case statement would be much cleaner here, i.e.

Case Select Target.Value
    Case 2
        'do this
    Case 3
        'do this
    Case Else
        'do this
End Select

Structuring it this way, you should avoid conflicts/fights between two sections of code. Only one of the colored sections will every run.

See here for more help on Case statements.

MS Excel: How to use the CASE Statement (VBA)

This Excel tutorial explains how to use the Excel CASE statement with syntax and examples. The Microsoft Excel CASE statement has the functionality of an IF-THEN-ELSE statement.

www.techonthenet.com