Function Not Working with Conditional Formatting

[sub80]

Hi;

Made a function which checks if the cell color is red or yellow and returns the word or returns nothing"". The sheet with the function looks into another workbook with conditional formatting which turns the cell into three/four colors dependent on the cell input. My issue is that the function does not work with cell coloring by conditional formatting. Works fine with regular cell color placement. How can I get it to see the coloring by the conditional formatting.

Code:
Function Checkcolor(range)
If range.Interior.ColorIndex = 3 Then
Checkcolor = "RED"
ElseIf range.Interior.ColorIndex = 6 Then
Checkcolor = "YELLOW"
Else
Checkcolor = "WHAT"
End If

End Function


Regards;

 

[hnsd24_CN]

If range.DisplayFormat.Interior.ColorIndex = 3 Then

 

[sub80]

Am getting #VALUE!

 

[sub80]

First of all *Hnsd24_CN thanks for fast reply.
Am getting #VALUE!

Found a similar topic but still has not resolved the issue: Function to return interior colour of conditional formatted cell

 

[RoryA]

If you are using the function in a cell, you cannot use the DisplayFormat property, so you have to evaluate each of the format conditions.

 

[rodgeraj]

I've got a really ugly way to do it...

Select the Range (column) of the cells you want to evaluate the color of the conditional formatting and then Run the Macro - ColorofRange

Sub GetCFcolor()
X = ActiveCell.DisplayFormat.Interior.Color
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = X
End Sub

Sub ColorofRange()
Dim c As Excel.Range
For Each c In Selection
GetCFcolor
ActiveCell.Offset(1, -1).Select
Next c
End Sub