Vba excel convert cell to Hebrew currency (ILS)

Question

I am trying to convert cells with numbers to Hebrew currency format, instead of getting the sign of currency I get question mark sign like in an example attached. I've rerecorded a macro but it just doesn't seem to work

Range("I4", "I" & LastRow).NumberFormat = _
    "_ [$?-40D] * #,##0.00_ ;_ [$?-40D] * -#,##0.00_ ;_ [$?-40D] * ""-""??_ ;_ @_ "

Thank in advance, I would appreciate a lot If someone could answer my question

Answer 1

The VBA editor doesn't recognize the Hebrew currency symbol so you can use an ASCII character instead. The one your looking for is ChrW(8362). I found the correct character code from this website.

Example:

ActiveCell.NumberFormat = _
"_ [$" & ChrW(8362) & "-10D] * #,##0.00_ ;_ [$" & ChrW(8362) & "-10D] * -#,##0.00_ ;_ [$" & ChrW(8362) & "-10D] * ""-""tt_ ;_ @_ "

---

Result: