Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Someone sent me an Excel 2010 spreadsheet with more than 50,000 records with the request to review the "highlighted in red" records. My current method of scrolling with the assumption I have a keen eye is not working well.

Is there a way to filter or find cells based on font color?

share|improve this question
1  
Do you want to filter or search? You can filter by colour (Just add the filter buttons) and search by font colour (Click "Options >>" in the search dialog and set the formatting as required). –  Jerry Sep 30 '13 at 13:22
    
@Jerry, text is highlighted. –  zundarz Sep 30 '13 at 13:25
    
@Jerry, I'd prefer to filter. My goal is to find the cells. I'm open to the method. –  zundarz Sep 30 '13 at 13:27
    
@Jerry Ah sorry, I haven't noticed your comment. Post it as an answer and I will delete mine. –  nixda Sep 30 '13 at 17:29
1  
@nixda Oh hey, no, that's okay, don't worry :) –  Jerry Sep 30 '13 at 17:30

2 Answers 2

up vote 4 down vote accepted

The feature to filter for color (font or background) is already implemented since Excel 2007.

enter image description here

Read more about it on: http://msdn.microsoft.com/en-us/library/cc952296.aspx.
Here is a Youtube video for the case you overlooked the filter option.

You tagged your question with Excel 2010, so I assume you have 2010 installed. Only 2003 won't have this option

share|improve this answer
    
nxida: In Excel 2010, my filter option looks like this: i.imgur.com/b1vctRx.png. How do I get the filter option you've shown? –  zundarz Sep 30 '13 at 19:44
    
The filter option in Excel 2010 is here: imgur.com/cBR1hGs. –  zundarz Sep 30 '13 at 19:53
    
@zundarz Have a look at the video: youtube.com/watch?v=_IgPAHe0mN8#t=167 –  nixda Sep 30 '13 at 19:55

This uses VBa.

Add the developer toolbar to the ribbon. In the Controls panel, click Insert and click on the button

Draw a button on your Excel work sheet. Click OK.

In the new Window, type the following so only this code is on the page (nothing else)

Sub Button1_Click()

Dim i As Integer
i = 1
For Each c In range("A1:A10") 'UPDATE THIS FOR THE CORRECT RANGE
    If c.Font.ColorIndex = 3 Then
        Range("M" & i).Formula = "=HYPERLINK(" & c.Address & ", " & c.Address & ")"  'CHOSE EMPTY COLUMN
        i = i + 1
    End If
Next

End Sub

In this example, I've chosen a free column (M) in this case. If M isn't free, then do change the M to a column which is free. You will also need to state the correct index range.

So, every time it finds a cell with the red font (with index of 3) it will write the value to the M column. So with your new list, you can navigate to the cell in question easily.

Big thank you to @mehow who suggested making the results clickable.

share|improve this answer
    
replace range("M" + CStr(i)).Value = c.Address 'CHOSE EMPTY COLUMN with Range("M" & i).Formula = "=HYPERLINK(" & c.Address & ", " & c.Address & ")" that will insert hyperlinks so you can click any of the hyperlinks to be automatically sent to the cell. I think that would be faster than manually scrolling through 50,000 records to find for example: F1502. –  vba4all Sep 30 '13 at 14:22

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.