Check if cell has custom format using UDF

[YasserKhalil]

Hello everyone
Is there a way that enables me using UDF for example to check if the cell has custom format or not?
It would return True or False
Example : A5 custom format ;;;
In B5 I imagine putting a UDF that checks A5 to see if it is formatted in custom format or not

Thanks advanced for help

[SamT]

What kind of custom format?
Font name or color?
cell color or border?
cell Horizontal or verticle alighment?
Number format?
Conditional format?
There are a dozen or so possibilities

[YasserKhalil]

Thanks a lot for reply
I mean when right-click on cell then Click "Format Cells" then select "Custom" ...

[mdmackillop]

Can you use the worksheet CELL function?

[YasserKhalil]

Can you use the worksheet CELL function?

I don't know a lot about it. Can you show me how to adapt CELL to that task? Or it is better to devise new UDF?

[mdmackillop]

See here

MsgBox Evaluate("Cell(" & Chr(34) & "Format" & Chr(34) & ", A1)")

[YasserKhalil]

That's great start point
I have formatted the cell A1 in that way ;;; and the result was "H" >>> I think it refers to hidden and this is not included in the link above
another point how can adapt this to check for the Custom only ... away from other customs. i think I may use Select Case to include all possible common formats but I can't figure it out

[mdmackillop]

Something like this : etc. line to be completed

Function MyFormatTest(Cel As Range) As String


Select Case Evaluate("Cell(" & Chr(34) & "Format" & Chr(34) & "," & Cel.Address & ")")
Case "G"
MyFormatTest = "General"
Case "F0", ",0" 'etc.
MyFormatTest = "Other"
Case Else
MyFormatTest = "Custom"
End Select
End Function

[YasserKhalil]

Yes that's exactly what I was searching for. Now it is solved
Thank you very much Mr. MD
Best Regards

[SamT]

Thanks a lot for reply
I mean when right-click on cell then Click "Format Cells" then select "Custom" ...

OK. That would be NumberFormat

Code for standard Module

Public Function HasCustomNumberFormat(Cell As Range) As Boolean
  HasCustomNumberFormat = Not Cell.NumberFormat = "General"
End Function

Returns True or False
Usage: to check Cell A1.
In another cell formula = "=HasCustomNumberFormat(A1) "

[YasserKhalil]

Thanks a lot SamT for reply .. But this UDF returns False to General only and any other format returns True ..
The main target is the Custom format .. and other formats would return False

[SamT]

Aah.

Well, it was written for Excel XP.

Use mdmackillop's.

[YasserKhalil]

Thanks a lot
What XP stands for? Forgive me as English is not my native language .. Do you mean experts?

[SamT]

Excel XP = Excel 2002

[YasserKhalil]

Thanks a lot and forgive me for my little experience