Autoformat currency to Accounting format without decimal

[Jagdev Singh]

Hi Experts

I am working on a macro which will cleans my raw data and cover all the aspect excluding the currency format. Is it possible for the VBA to check the columns with currency and on find convert the format of the entire column to Accounting with 0 decimal? Is it possible via VBA?

Regards,
JD

 

[Deepak]

JD, Can we have the sample pls.

 

[Jagdev Singh]

Hi Deepak

Please find the sample file attached with the thread. The column location is not fixed.

Regards,
JD

 

[Deepak]

Hi JD,

Is this...

Sub Check_Format()
Dim r As Range
  For Each r In Range("a2:F2")
  'Choose Any one
    If VarType(r.Value) = vbCurrency Then r.Columns.EntireColumn.NumberFormat = "$#,##0"
 '   If InStr(r.NumberFormat, "$") Then r.Columns.EntireColumn.NumberFormat = "$#,##0"
  Next
End Sub

 

[Jagdev Singh]

Hi Deepak

Thanks for the code, but I not able to get the desire result. Please find the sample file attached with the desire output it it.

Regards,
JD

 

[Jagdev Singh]

Hi Deepak

I solved the query. I just have to change the currency sign in the code and it worked like a charm.

Thanks for the code.

Regards,
JD

 

[Jagdev Singh]

Hi Deepak

Can we also check and highlight the empty cell in the currency columns.

Regards,

JD

 

[Deepak]

JD

Sub Check_Format_v2()
Dim r As Range
  For Each r In Range("a2:F2")
  If VarType(r.Value) <> vbCurrency Then GoTo n
    With r.Resize(, 1)
        .NumberFormat = "$#,##0"
        .SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow
    End With
n: Next
End Sub

 

[Jagdev Singh]

Hi Deepak

The above code is checking the row A2:F2 and not filling the blank cell in the row. Please lemme know if I am doing something wrong in it.

Regards,

JD

 

[Deepak]

Your wb cells having value 0 not blank.

delete some cells values & check it

[Sheet1!$A$1:$F$14].SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow

 

[Jagdev Singh]

Hi Deepak

You second code is working fine, because we are giving the range as a whole. When I am trying to use the code to search the currency code again it won’t gimme the result. Please find the sample attached.

Regards,

JD

 

[Deepak]

Your data type is not in currency format

 

[Jagdev Singh]

Hi Deepak

Sorry for the confusion, I want to know where I am going wrong. I have uploaded the live data as a sample in the thread. We have to perform 2 exercises

1. Change the format to accounting
   
   
2. If blank found highlight the cell
   

Regards,

JD

 

[Deepak]

Is the columns/header are fixed or if everything is dynamic then how VBA will determine that which columns needs to be format.

Simply to highlight!!

Activesheet.usedrange.SpecialCells(xlCellTypeBlanks).Interior.Color = 0

 

[Jagdev Singh]

Hi Deepak

The columns and headers are dynamic. Your above formatting code where we are catching the range (a2:F2) is fine, but blank cell in specific currency column is were we are getting stuck.

Regards,
JD

 

[Deepak]

Ok.i will check for a workaround tomorrow.