CFColorindex Function not working in 2007.

[rishidoshi]

Hi,
I am using CFColorindex function to find out the current colorindex of a cell based on the condition format state.
It works perfectly in office 2003 but not in office 2007/10. Download The excel file
The line: Select Case oFC.Operator returns an error: Runtime 1004: Application defined or object defined error.
Also, the variable in the line: CFColorindex = (rng.Value < CSng(oFC.Formula1) Or _
rng.Value > CSng(oFC.Formula2))

variable: oFC.Formula2 shows "Application defined or object defined error." When i take the mouse over it.
​

Please help me sort this out.
Or is there a new version of this function available to get the colorindex of a CF'ed cell?
(I did google a lot but couldnt find a solution)
Thanks...
Rishi

Public Function CFColorindex(rng As Range)
'---------------------------------------------------------------------
Dim oFC As FormatCondition
Dim sF1 As String
Dim iRow As Long
Dim iColumn As Long

   Set rng = rng(1, 1)
   If rng.FormatConditions.Count > 0 Then
      For Each oFC In rng.FormatConditions
         If oFC.Type = xlCellValue Then
            [COLOR=#ff0000][B]Select Case oFC.Operator[/B][/COLOR]
               Case xlEqual
                  CFColorindex = rng.Value = oFC.Formula1
               Case xlNotEqual
                  CFColorindex = rng.Value <> CSng(oFC.Formula1)
               Case xlGreater
                  CFColorindex = rng.Value > CSng(oFC.Formula1)
               Case xlGreaterEqual
                  CFColorindex = rng.Value >= CSng(oFC.Formula1)
               Case xlLess
                  CFColorindex = rng.Value < CSng(oFC.Formula1)
               Case xlLessEqual
                  CFColorindex = rng.Value <= CSng(oFC.Formula1)
               Case xlBetween
                  CFColorindex = (rng.Value >= CSng(oFC.Formula1) And _
                             rng.Value <= CSng(oFC.Formula2))
               Case xlNotBetween
                  CFColorindex = (rng.Value < CSng(oFC.Formula1) Or _
                             rng.Value > CSng([COLOR=#ff0000][B]oFC.Formula2[/B][/COLOR]))
            End Select
         Else
             're-adjust the formula back to the formula that applies
             'to the cell as relative formulae adjust to the activecell
            With Application
               iRow = rng.Row
               iColumn = rng.Column
               sF1 = .Substitute(oFC.Formula1, "ROW()", iRow)
               sF1 = .Substitute(sF1, "COLUMN()", iColumn)
               sF1 = .ConvertFormula(sF1, xlA1, xlR1C1)
               sF1 = .ConvertFormula(sF1, xlR1C1, xlA1, , rng)
            End With
            CFColorindex = rng.Parent.Evaluate(sF1)
            If rng.Value = "" Then CFColorindex = False
         End If

         If CFColorindex Then
            If Not IsNull(oFC.Interior.ColorIndex) Then
               CFColorindex = oFC.Interior.ColorIndex
               If rng.Value = "" Then CFColorindex = False
               Exit Function
            End If
         End If
     Next oFC
   End If 'rng.FormatConditions.Count > 0

End Function

 

[Jerry Sullivan]

Hi Rishi,

You might be interested in Rick Rothstein's code that reads a cell's displayed color whether or not it is applied conditionally.

Get Displayed Cell Color (whether from Conditional Formatting or not)

 

[rishidoshi]

Hi,
I am getting the same error on the same line:
code: Select Case .Operator

 

[Jerry Sullivan]

In taking a look at your file, the problem is that VBA is inconsistently returning the incorrect FormatConditions.Type.

Your worksheet is using three formula-based rules:
=NOT(MOD(A4-$I$1,4)) --> if True, format with Blue fill
=NOT(MOD(A4-$I$1,4)-1) -> if True, format with Red fill
=NOT(MOD(A4-$I$1,4)-2) -> if True, format with Green fill

The test for .Type should be equal to xlExpression or 2;
but instead VBA, is evaluating these as .Type=xlCellValue or 1

You can easily confirm this in the Immediate Window by entering the expression:
?Range("A4").FormatConditions(1).Type
Returns 1

If you change the first rule to...
=NOT(MOD(0-$I$1,4))
then ?Range("A4").FormatConditions(1).Type
Returns 2

The interesting thing is that if you then change the first rule back to...
=NOT(MOD(A4-$I$1,4))
then ?Range("A4").FormatConditions(1).Type
Now Returns 2

Suggested workaround:
If you change your CF formulas to include +0, the CFColorindex UDF gets past the .Operator error, but errors later in the code.
I'd suggest switching to Rick's "DisplayedColor" UDF, which seems to work if you add +0 to your CF formula.

Rick's UDF could be adjusted to handle this apparent bug by testing whether .Operator returns an error, and if so, treating the .Type as xlExpression even if it is returning xlCellValue

 

[Jerry Sullivan]

I had a typo above. This should read:

If you change the first rule to...
=NOT(MOD(0+A4-$I$1,4))
then ?Range("A4").FormatConditions(1).Type
Returns 2


So, try changing your 3 CF formulas to:
=NOT(MOD(0+A4-$I$1,4)) --> if True, format with Blue fill
=NOT(MOD(0+A4-$I$1,4)-1) --> if True, format with Red fill
=NOT(MOD(0+A4-$I$1,4)-2) --> if True, format with Green fill

 

[rishidoshi]

Hi,
I did what you suggested (I think). The code now does not return an error. (WOW)
But my design is such that if neither RED, BLUE or GREEN appear, then the default color of the cell is ORANGE (manually assigned to each cell).
This was done since only 3 CF can be assigned. So if none of the 3 are active, then Orange (the default cell color) is returned.
How do i handle this? Currently the code returns a full ORANGE color for all cells. (Download the new excel file)

btw i am really happy to see responses coming through. Mega thanks JS411!

 

[Jerry Sullivan]

The link to your Excel file isn't working. Please check to see if it is the correct link.

In xl2007 you are not restricted to 3 rules so one option would be to use a fourth rule.

That being said, we should be able to have the cell color remain Orange if none of the rules are True.

I'll download your file when it becomes available and try to help.

 

[rishidoshi]

I'm sorry i forgot to enable 'public' in the sharing. It should work now.
I also tried to use 4 CF rules and deleted the default color (orange) from every cell. Now it returns all BLUE download LINK
(pls ignore the PM)
EDIT: I have enabled the "stop if true" in the CF rules. Does that affect anything?

 

[Jerry Sullivan]

I was able to download your file and the problem seems to be related to the way the DisplayedColor function tries to read the CF Formula1 Property relative to the cell whose displayed color we want to find.

Rick explains that in his first post (where he explains why this can't be used as a UDF).
Unfortuntately, in this case at least, Formula1 is returning a static formula like:
=NOT(MOD(0+B4-$I$1,4))
Instead adjusting the formula relative to the postion of the cell
=NOT(MOD(0+C4-$I$1,4))
=NOT(MOD(0+D4-$I$1,4))
=NOT(MOD(0+E4-$I$1,4))
...for Cells C4, D4 and E4

That's why it is returning all Orange or all Blue - it's only evaluating Cell B4.

It's getting late here in San Diego. I'll look at this again tomorrow unless you or Rick or someone else figures it out before then.

 

[rishidoshi]

Aah! You are the man! I needed colors only from cells W6 to AB6 in the "PLLdrill" sheet. I removed the CF form them and 'Paste special-ed' the formats from cell B4. IT IS WORKING NOW!!!!!! the formulas are referring to the self cell now and not B4. I cant thank you enough for this. I'd like to buy you a drink sir!
Thanks. The file for you reference. The 'Previous' button is not working as that selects the cells from the main column, B:G. waiting for it to work globally.
(I wont say that the code is perfect but at least my problem is solved)