Create a number format with criteria by using a loop every row-CodePudding

I want to make a number format with criteria if USD = 4 Digits, IDR = 0 Digits, JPY = 2 Digits using macros in excel because there are a lot of rows

Sample

Thanks for the help

I've used this module but it doesn't work

Sub LoopRange()
    Dim rng As Range
    Set rng = Range("A1:A100")
    For Each cell In rng
        If Range("a1").Value = "IDR" Then
        Range("b:b").NumberFormat = "#,##0"
    ElseIf Range("a1").Value = "JPY" Then
        Range("b:b").NumberFormat = "#,##0.00"
    Else: Range("b:b").NumberFormat = "#,##0.0000"
    End If
    Next cell
End Sub

CodePudding user response：

For Each cell In rng already loops trough specific cell, no need to specify later Range("a1"). Furthermore, you are looping only trough A1, I think yoi mean cell

Also, Range("b:b").NumberFormat will change the format of the whole column. I think you want cell.Offset(0,1).NumberFormat or Range("B" & cell.row)

Probably you want something like this:

Sub LoopRange()
Dim rng As Range
Dim cell As Range
Set rng = Range("A1:A100")

For Each cell In rng
    If cell.Value = "IDR" Then
        cell.Offset(0, 1).NumberFormat = "#,##0"
    ElseIf cell.Value = "JPY" Then
        cell.Offset(0, 1).NumberFormat = "#,##0.00"
    Else
        cell.Offset(0, 1).NumberFormat = "#,##0.0000"
    End If
Next cell

Set rng = Nothing

End Sub

CodePudding user response：

You can use conditional formatting as well: