Trapping colorindex value of CFormatted cell (2000)

Topic

New Reply

I have been trying to run some VBA code using the colorindex value of conditionally formatted cells to return row numbers and other information about the location of that cell. For the past hour I have been trying to figure out why my code will not work, only to discover that conditional formatting does not change the .interior.colorindex value of the cell. All cells which change interior color as a result of conditional formatting show a color index value of -4142. Has anyone else had this problem?

Reply | Quote

Replies

I think you are confusing Selection.Interior.ColorIndex with Selection.FormatConditions(n).Interior.ColorIndex – have a look at the FormatConditions object in the vba help and see if that’s what you’re after.

Brooke

Reply | Quote

I think I see some of what you’re saying Brooke. I am still puzzled as to why the following will not work:

Public Sub VarList()
Dim ST As Long
'ThisWorkbook.Sheets("Variance List").Visible = True
'ThisWorkbook.Sheets("Variance List").Activate
For Each cell In ThisWorkbook.Sheets("Deposit Rec").Range("e4:e38")
    With cell
           If .Interior.ColorIndex  xlColorIndexNone Then
                ST = cell.Value
                PrintVar (ST)
            End If
    End With
Next
End Sub

If conditional formatting actually changes the interior color of the cell, a person would think that Selection.Interior.ColorIndex would return the proper value. I am trying to return the colorindex of the cell after it has been changed via conditional formatting.

If I put .ConditionalFormats(1).Interior.ColorIndex, this would return the colorindex of the format should the condition apply. Y? N?

Reply | Quote

Mike,

I think you best approach would be to test for the condition that gives rise to the color being applied, for example if cells < 100 are colored red, and you want to do something with th ered cells then check for values less than 100 etc. The actual colorindex of a cell remains the same regardless of conditional formatting.

Using .ConditionalFormats(1).Interior.ColorIndex will return the actual color index that applies to condition 1, whether ithe condition is met or not.

Andrew

Reply | Quote