Excel 2010 Find/Replace Formatted Cells

[SumDood]

Sorry to post this here, but I'm at my wit's end.

So I need to mark all cells in my sheet that have a Red background with an 'X'.

I use Ctrl+F, choose replace, and under the Format button I select 'Choose Format From Cell'

Works fine if I select a cell that I set the background myself.

I have a sheet from a client where this process does not work at all.

I can copy a single cell from the original (from the client), put it on a brand new sheet in a brand new workbook, and it still won't work. I can then manually fill a cell with a color, and the process works fine.

I get the following error:

"Microsoft Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly. If you are sure that matching data exists in this workbook, it may be on a protected sheet. Excel cannot replace data on a protected worksheet"

Please help before I smash my machine.


Excel 2010, Window 7, if it matters

[DrDave]

Only thing I can think of (which may be completely wrong) - is the client sheet using conditional formatting rather than a background colour?

I would check if that makes any difference but my laptop is stuck on windows updates

[BowlOfRed]

Originally Posted by SumDood

I can copy a single cell from the original (from the client), put it on a brand new sheet in a brand new workbook, and it still won't work. I can then manually fill a cell with a color, and the process works fine.

By "works fine", do you mean it finds (only) the cell you manually filled, or do you mean that after manually filling one it now finds all the red cells (including those that were not found before)?

[SumDood]

Originally Posted by BowlOfRed

By "works fine", do you mean it finds (only) the cell you manually filled, or do you mean that after manually filling one it now finds all the red cells (including those that were not found before)?

If i use the manually formatted cell as the find/replace criteria, it will find/replace other manually formatted cells with the same format. If i try it with one of the cells from the 'original' (client) sheet, it finds nothing and I get the popup error message.

[Bindamel]

Originally Posted by DrDave

Only thing I can think of (which may be completely wrong) - is the client sheet using conditional formatting rather than a background colour?

I would check if that makes any difference but my laptop is stuck on windows updates

I just tested this, and if it's conditionally formatted, Excel 2010 cannot find the cells. So if the client is using conditional formatting to make the red background, you won't be able to do a find/replace on it, it seems.

[BowlOfRed]

Originally Posted by SumDood

If i use the manually formatted cell as the find/replace criteria, it will find/replace other manually formatted cells with the same format. If i try it with one of the cells from the 'original' (client) sheet, it finds nothing and I get the popup error message.

While I don't immediately have any idea what's going on with your system, I think I can rule out it having anything to do with conditional formatting. I'm unable to find a conditionally formatted cell at all, so it doesn't sound like your case.

[Pantaz]

Originally Posted by SumDood

... So I need to mark all cells in my sheet that have a Red background with an 'X'. ...

What conditions cause the cell to have a red background?

[Foolmewunz]

Originally Posted by Pantaz

What conditions cause the cell to have a red background?

This ^^^. Computers being cranky old beasts, you need to ask just how it became red in the first place. And since you seem to want to find the cells that they found and that they made red, you might be able to piggyback on their efforts.

I'm not an expert at Excel - not by a longshot - but as far as I know the only way that cells in a functioning worksheet can have a background color is either through individual formatting or conditional formatting. (Short of painted objects that are inserted as a picture or table.) An individual cell (unless it's protected?) that's been individually formatted can be changed. In Excel 2010, a cell (or cells) that's been subjected to conditional formatting can only be changed by turning off the conditional formatting. Go to the home screen, look in the "styles" box (third or fourth collection from the left) and click on Conditional Formatting. It will then allow you to remove the "rules" (which would be how the formatting came about) and the color would disappear. You can do it for the cell you're working on or you can do it for the whole sheet. If you do the latter, though, you won't have any more red cells to search for.

Ergo, it would be better if you could determine what Rules they put in there and see if you can just rewrite the rule to put an X there. (I'm not sure what you mean by "need to mark them with X", though. As a note? )


Other possibility - I glossed over above.... Can you change the formulas or data in those cells? Maybe it's protected? (That'd be the overly easy answer and I'm pretty sure that Excel tells you if you're mucking about with a protected cell. It does not, however, give any indication if a cell has conditional formatting - at least not that I can determine. Regular formatting you can only see by looking at the various indicators. To me, it's one of the minor foibles of Excel. I'd like to see some kind of superscript or radio button that allowed you to enquire as to all the formatting done to a given cell.)

[SumDood]

I really appreciate all the help.

There's no conditional formatting and the cells are not protected. I can upload/email an example if someone wants to get a hands-on look.

This is typical for my job. A client give us a spread sheet and we have to load the contents into our database. On this particular sheet, there are columns that have a status that is color coded. We are only interested in the ones that are red and yellow. I'm pretty sure the original workbook was manually created (as opposed to some sort of data dump from another system).

[DrDave]

As a workaround while the problem is investigated, can you use the auto filter and filter by colour and then edit the cells manually (should be nice and quick with a copy paste).

For the future, can you ask them to enter the words red green etc?!

[SumDood]

Got a macro that fills in the x's perfectly. And great relief was felt.

I'm still curious about the original issue, so here's a link to an example I uploaded:

http://www.mediafire.com/?f5yj441jpa9jw1q

[Wudang]

OK I'd just confirmed that in VBA/macro terms they both show same Interior.Colorindex Useful link here http://www.cpearson.com/excel/colors.aspx

[rjh01]

I can find the red colour and put an X in those colours. However there is a difference in the two reds. If you go to Format cells and click on the fill tab one set is red, the other is not defined.

Edit. But go to more colours... and both cells are defined as a colour. What your idiot client has done is use a non standard colour.

[Beerina]

I've been playing with conditional formatting lately for cell colors. I've noticed that you can set the color via formula, but if you paste in or set a different color manually, it overrides the conditional formatting for that cell.



It suggests to me that Excel uses a "default" for a cell (which may be default for the column or whole worksheet), and also tracks in a sparse manner (computer programming term, sparse matrix) individual manual settings.

This conditional formatting is yet a third method that sits atop all that unless overridden. And the find function is only bright enough to say "Check the manual override first, then the default value" and it's not bright enough to then fall back to the conditional formatting if those two fail.

[Zax63]

Originally Posted by rjh01

I can find the red colour and put an X in those colours. However there is a difference in the two reds. If you go to Format cells and click on the fill tab one set is red, the other is not defined.

Edit. But go to more colours... and both cells are defined as a colour. What your idiot client has done is use a non standard colour.

I agree with rjh01, and as Wudang said they both return the same color index(3 in this case) if you use VBA. You could set up this function^*:

Function BGCol(MRow As Integer, MCol As Integer) As Long
    BGCol = Cells(MRow, MCol).Interior.ColorIndex
End Function

^*Downloaded from here

Then you can do a column with the formula =BGCol(row,column). Both row & column have to be numbers, so column A=1, B=2...
I prefer to use =BGCol(ROW(),COLUMN()+offset) so you can copy the formula more easily.

Then you can search for 3's instead of searching for red. It's not perfect, as you can see from the attached screen shot the red in row 16 returns a 9 and not a 3 as it is has it's own entry in the color index.

I suppose you could always do something like =IF(BGColor(ROW(),COLUMN()-1)<> -4142,"Highlighted","White")
as long as you don't mind getting every row that has a background color. I'm assuming the highlights are in one column or one complete row and not randomly scattered around a spreadsheet.

Now that I think about it you could use this in VBA to put an X in any cell that has a background color. This will work with randomly scattered cells. You could sub = 3 for <> -4142 to do just the reds. Careful!!! It will overwrite anything in a colored cell with an X. If you use it be sure to save to a different file name before running in case you get unwanted results.

Sub BackgroundX()
    Dim lRow As Long
    Dim lCol As Long
    
    For lRow = 1 To ActiveSheet.UsedRange.Rows.Count
        For lCol = 1 To ActiveSheet.UsedRange.Columns.Count
            If Cells(lRow, lCol).Interior.ColorIndex <> -4142 Then
                ActiveSheet.Cells(lRow, lCol).Value = "X"
            End If
        Next lCol
    Next lRow
            
End Sub