CODEWITHSUNDEEP
excelvba
learnTech
learnTech

Reputation: 45

font color of data validation type cell

I have a data validation drop downs in my excel. if some one copy - paste invalid data into these dropdowns, if the pasted value is not matched with dropdown data I am highlighting text in red using conditional formatting . Now I am trying to get the text color of this dropdown in case of mis matched data using VBA. but I am getting -4105 as the color index.

Function GetColorText(pRange As Range) As String

Dim xOut As Variant
Dim xValue As String
Dim i As Long
xValue = pRange.Text
MsgBox "cell value = " & xValue

xOut = pRange.DisplayFormat.fornt.Color
MsgBox "Color  = " & xOut

GetColorText = xOut
End Function

Can some one help to get the text color in case of mis mached data

Upvotes: 0

Views: 239

Answers (1)

FunThomas
FunThomas

Reputation: 29286

Using DisplayFormat in a UDF is not supported, see https://learn.microsoft.com/en-us/office/vba/api/excel.range.displayformat.

There is a workaround using Evaluate, see https://stackoverflow.com/a/54757688/7599798. In your case, simple replace c.DisplayFormat.Interior.Color with c.DisplayFormat.Font.ColorIndex.

Function GetColorText(pRange As Range) As String
    if isempty(pRange) exit function 
    Dim xOut As Variant
    xOut = pRange.Parent.Evaluate("DFColor(" & pRange.Address() & ")")
    GetColorText = xOut
    Debug.Print pRange.Address; xOut
End Function

Function DFColor(pRange As Range)
    DFColor = pRange.DisplayFormat.Font.ColorIndex
End Function

Update If you don't want to return anything if the cell is empty, just add a check at the top of the routine. See the updated the code.

Upvotes: 1

Related Questions