CODEWITHSUNDEEP
excelvba
Helloguys
Helloguys

Reputation: 289

Excel VBA - User Defined Function in Condictional Formatting

I built a User Defined Function with VBA code and it works fine in Excel if I use it in a formula to populate cell values. However, I cannot use this function in conditional formatting. I'm getting message "This type of reference cannot be used in a Conditional Formatting formula".

I'm curious if any UDF can be used in conditional formatting? Or no UDF is allowed at all? Thanks!

enter image description here

enter image description here

UDF Codes is as below:

Function isValidMAC(mac As String) As Boolean

Dim regex As New RegExp
Dim strPattern As String

' Exact 12 characters.  Valid characters are a-f or A-F or 0-9
strPattern = "^[a-fA-F0-9]{12}$"

With regex
    .Global = False
    .MultiLine = False
    .IgnoreCase = False
    .Pattern = strPattern
End With

isValidMAC = regex.Test(mac)

End Function

Upvotes: 4

Views: 836

Answers (1)

EvR
EvR

Reputation: 3498

As your UDF is not is the same workbook, but in your add-in, you will receive this message; As a workaround: use your UDF-call in a defined name and use this Defined Name in your conditional Formatting Formula

Upvotes: 4

Related Questions