CODEWITHSUNDEEP
excelvba
nikolaikolev
nikolaikolev

Reputation: 27

Add currency sign to prices

In Excel, I have a range R1:W300 with variety of cells with prices, dates, percents, text etc.

I want only after all prices to put the currency sign.

I use the code below but it puts the currency sign to all data - not only the prices. How can I make it put the currency sign only on the prices?

Sub FormatCurrency()    
    Dim Sh As Worksheet   
    Set Sh = ThisWorkbook.Sheets(1)    

    Sh.Range("R1:W300").NumberFormat = "#,##0.00 $"
End Sub

Upvotes: 1

Views: 72

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

A simple loop and an If statement like below should do:

Option Explicit

Public Sub FormatCurrency()    
    Dim Sh As Worksheet   
    Set Sh = ThisWorkbook.Sheets(1)    

    Dim Cell As Range
    For Each Cell In Sh.Range("R1:W300").Cells 'loop through all cells in that range
        If Cell.NumberFormat = "General" Then  'check if numberformat is General
            Cell.NumberFormat = "#,##0.00 $"   'if general change it to currency
        End If
    Next Cell
End Sub

Upvotes: 1

Related Questions