Excel Color Functions

[AlexCS]

Hi everyone,

I need one of my cells to take a certain value based on another cell's background color that is assigned via conditional formatting. Finding a function that returns the colorindex of a cell was relatively easy - the problem is that this function returns -4142 when it comes accross conditionally formatted cells (which is the code for no fill), although the visible colorindex for that cell is 42.

Does anyone know how to make the below function work on cells colored via conditional formatting?

Many thanks,

Alex

Function ColorIndexOfOneCell(Cell As Range, OfText As Boolean) As Long

Dim CI As Long

Application.Volatile True
If OfText = True Then
CI = Cell(1, 1).Font.ColorIndex
ColorIndexOfOneCell = CI
Else
CI = Cell(1, 1).Interior.ColorIndex
ColorIndexOfOneCell = CI
End If

End Function

 

[shg]

No, I was referring to the more recent link.

Your code exits on the first True result, but what if that rule doesn't have any interior formatting, but a later one, also true, does?

I think changing the Evaluate to Cell.Worksheet.Evaluate does resolve some of the other issues (and eliminates the need to select the cell), and would allow it to work as a UDF and reference across sheets.

 

[Rick Rothstein]

Your code exits on the first True result, but what if that rule doesn't have any interior formatting, but a later one, also true, does?

Good point!!! I had completely overlooked that possibility/probability.

I think changing the Evaluate to Cell.Worksheet.Evaluate does resolve some of the other issues (and eliminates the need to select the cell), and would allow it to work as a UDF and reference across sheets.

I'll play around with that idea. Thanks for mentioning it.

 

[shg]

What seems more difficult is knowing whether a format includes fill -- at a glance, I don't see anything in the object model that tells you.

 

[AlexCS]

Thanks Rick and shg for not giving up on this after I had given up myself since I hardly have to deal with such complications.

After reading the explanations in your last posts, I have a much better understanding of how the code works thanks again!

Alex