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

 

[VoG]

This may help you http://www.cpearson.com/excel/CFColors.htm

 

[AlexCS]

Hi Peter,

Thanks a lot for the link..the proposed function is quite complicated so still working on it

Regards,

Alex

 

[Rick Rothstein]

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.

Here is a function that I wrote which sounds like it should be useful to you...

Get Displayed Cell Color - PART 2 (It can now be used as a UDF)
.
.

 

[Rick Rothstein]

Here is a function that I wrote which sounds like it should be useful to you...

Get Displayed Cell Color - PART 2 (It can now be used as a UDF)
.
.

The code at that link has now been marked as "flawed".

 

[AlexCS]

Thanks for trying to help Rick..I have given up in the meantime, thankfully my users decided they can do without the respective function

Alex

 

[Rick Rothstein]

Thanks for trying to help Rick..I have given up in the meantime, thankfully my users decided they can do without the respective function

If you do not need the function to work as a UDF (that is, you do not need it to work in a formula on the worksheet), then the function at this link can be used... it works when called from from other VB code meaning it can be used in a macro or event code.

http://www.excelfox.com/forum/f22/get-displayed-cell-color-whether-conditional-formatting-not-338/

 

[shg]

Perhaps the Evaluate functions should be changed to Cell.Worksheet.Evaluate?

 

[shg]

Ah, never mind -- it's designed only for a cell on the active sheet.

 

[Rick Rothstein]

Perhaps the Evaluate functions should be changed to Cell.Worksheet.Evaluate?

The threaded view showed you replied to my message. I am guessing you were referring to my post about the flawed code I originally posted about. The "flaw" has to do with the fact that when you examine the formula(s) for Conditional Formatting, any cells in the formula with relative row and/or column referencing are referenced to the ActiveCell, not the cell your code is built off of. For Conditional Formatting relations that are not formula based and for formula based ones where all the cell references in the formula are absolute, the "flawed" code I posted works perfectly (which is why I thought I had a workable solution initially), but as soon as a Conditional Formatting relation uses a formula with relative referencing, all bets are off. The reason the code at the last link works is because it is not used as a UDF meaning I can move focus to the ActiveCell temporarily in order to let Evaluate calculate whether the formula is true or not. I thought I might be able to patch the "flawed" code by parsing the formula for relative cell references and adjusting them to the ActiveCell (Evaluate uses a String argument, so that action would be allowed in a UDF), but I think I will have to give up on that idea given my realization of the complications that using Defined Names would involve. After my attempts to create my UDF, I now only can shake my head in wonderment how Excel manages to implement its Conditional Formatting in the first place.