Formatting all 1 as ✔ and all 0 as Blank (with vba ? )

[JumboCactuar]

Hi,
i have the following working directly on worksheet

✔;;;@

but the problem is when trying to reapply the formatting with VBA, as the tick isnt recognised.

example, when i record myself applying the formatting, the output is:

Range("A1:A23").NumberFormat = "?;;;@"

Does anyone have a way around this ?
Reason i need to reapply with VBA is due to formatting being lost when web table is refreshed

Any help appreciated


update:
i realised i can work around this by having a set column with the formatting, then VBA to copy the formats over, like:

Sub Macro2()


    Range("A1:A17").Copy: Range("I1").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


End Sub

though still looking for a better solution
thankyou

 

[Rick Rothstein]

Try it this way...

Range("A1:A23").NumberFormat = ChrW(10004) & ";;;@"

 

[JumboCactuar]

Try it this way...

Range("A1:A23").NumberFormat = ChrW(10004) & ";;;@"

Perfect it works, thankyou