is there a possible way to assign a dropdown menu a target adress?

[kafa1500]

hi, everyone.. i am a newbie in Excel VBA macro codes, and i' ve stucked in a macro code.

I' ve set a form control combobox (drop_down menu) and with index formula i've tied this combobox to range(B56:B52) and celllink (G53). Every item in combobox has different style or number format. (For example "A" item datas are %percent, and "B" item datas are "currency". When i work with data validation, because of i can assign a target adress, i can change the data formats when i change item in data validation (not: i tied data validation with drop down also to allow datas change). However, i can't assign a target adress into dropdown menu. So when i change items in drop down , the datas for the listitems change but data formats don't.

Cell for Data validation is $F$56
Range that i've written Index formula in : "B56:B82"
Index formula reference value (cell link): $G$53
Data Validation Source: $D$55:$D$59
Drop down menu input range: $D$55:$D$59

Codes:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$F$56" Then
Application.EnableEvents = False
If Target.Value = "A" Then
Range("$G$53").Value = "1"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
ElseIf Target.Value = "B" Then
Range("$G$53").Value = "2"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
ElseIf Target.Value = "C" Then
Range("$G$53").Value = "3"
Range("B56:B82").NumberFormat = "#,##0.00 TL"
ElseIf Target.Value = "D" Then
Range("$G$53").Value = "4"
Range("B56:B82").Style = "Percent"
ElseIf Target.Value = "E" Then
Range("$G$53").Value = "5"
Range("B56:B82").Style = "Comma"
End If
Application.EnableEvents = True
End If
End Sub


I am working on it for nearly 3 days but still can't success. Can anyone help me in this?

Thanks.