Formula to check if a cell is highlighted

[financialdoc]

Is there a formula I can use to see if a cell is highlighted? I need to filter for highlighted cells and want to use a formula to produce a binary result (1 for highlighted or 0 for no highlighting) so I can filter on that to only the highlighted cells.

Thanks in advance for any replies!

 

[texasalynn]

take a look at
http://www.cpearson.com/excel/colors.htm

 

[lenze]

Using Chip's function from the above cited site

Function CellColorIndex(InRange As Range, Optional _ 
    OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior 
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
    CellColorIndex = InRange(1,1).Font.ColorIndex
Else
    CellColorIndex = InRange(1,1).Interior.ColorIndex
End If

End Function

you can generate the binary with

=IF(CELLCORINDEX(A1,FALSE)>0,1,0)

lenze

Edit: Corrected typo in formula

 

[financialdoc]

Thanks!! That works perfectly!!

 

[financialdoc]

After importing the function, I used the formula:

=IF(CellColorIndex(R2,FALSE)>0,1,0)

Thanks!! That works perfectly!!

 

[chrs0049]

For some reason the above code doesn't work for cells that are highlighted as a result of conditional formatting. Anyone have a solve for that?

 

[Haytch114]

I had to do the same thing you were doing. I dont think there is a straight forward formula like vlookup, sum, countif ect. so unfortunately it requires a macro.

Create a macro and here is the code I used:

Sub COUNT_HIGHLIGHTS()
'
' COUNT_HIGHLIGHTS
'


'Defining variables
Dim LastRow As Long, Count As Integer


'Getting a number value for the number of rows in the table (as this can vary on table size)
LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row


'For loop to loop through rows
For i = 2 To LastRow
'Count is the number of cells in the row which are highlighted
Count = 0
'For loop for cells within the row (My table always has 36 cells)
For j = 5 To 36
'If statement to check if cell is highlighted
If Cells(i, j).Interior.Color = 65535 Then
'Add +1 to count ever time a highlighted cell is found
Count = Count + 1
End If
Next j
'find cell at the end of the row and add the count
Cells(i, 37).Select
Selection.Value = Count
Next i
End Sub

Once you finish you get a column at the end

Column 1 | Column 2 | Column 3 | ...... Column just outside your table
ColValue 1 | ColValue2| ColValue3| ....... 5 (If 5 cells in the table are highlighted)

Then just filter the table to the count values you created to everything but 0

Hope this helps