CODEWITHSUNDEEP
vbaexcel
user3479230
user3479230

Reputation: 13

Excel VBA Change Number format on Cell value change

I am having a little difficulty with some Excel visual basic. Particularly I want to change the format of a column based on the value of a cell. Here's what I have so far.

 Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("$b$1") Then
        If Range("$b$1") = "Date" Then
            Range("d:d").NumberFormat = "m/d/yyyy"
        ElseIf Range("$b$1") = "Number" Then
            Range("d:d").NumberFormat = "#,##0.00"
        End If
    End If
End Sub

I am new to using the Target feature so any advice would be helpful.

Upvotes: 1

Views: 61411

Answers (2)

LeafOnWind
LeafOnWind

Reputation: 1

If you are looking to tell if a particular cell or range of cells this code works

If Not Intersect(Target, Range("A1")) Is Nothing Then
    <operations>
End If

This will trigger the < operations > ONLY if the target range intersects range A1. If a macro runs and makes any change to the sheet the option to undo gets wiped out.

Hope this helps.

Upvotes: 0

coni2k
coni2k

Reputation: 2585

I don't think you need to use Target, just the block inside that check should enough. Like this;

Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("B1") = "Date" Then
        Range("D:D").NumberFormat = "m/d/yyyy"
    ElseIf Range("B1") = "Number" Then
        Range("D:D").NumberFormat = "#,##0.00"
    End If

End Sub

Then change the value in B1 to "Date" or "Number".

Upvotes: 6

Related Questions