excel find/replace formatted cells

Question

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

Answer 1

Hi,

When i pressed Ctrl+1 on the range (which you marked as NOT WORKING), i notied that the box for Wrap text was also checked (in addition to the background colour of Red).  So this is what i did:

1. Press Ctrl+F
2. Ensure that the Find box is empty
3. Click on Options > Format > Format
4. In the Format box, i checked the box for Wrap text on the Alignment tab (ensure other two boxes on Alignment tab are unchecked).  I also selected red colour on the Fill tab.  Then click on OK
5. Press Find All

In the white space below (in the Find box), i then saw the four red coloured cells.

I tried this in Excel 2010.

Answer 2

Arggggggg! 

Here are a couple of macros that may help accomplish the task or at least find out what the deal is.

To put them into A COPY OF ONE OF THE WORKBOOKS for trial, follow the instructions on this page:

http://www.contextures.com/xlvba01.html#Regular

Try the FindInteriorColorCode() routine first: select one of those shaded cells and run it and see what value it returns.  -4142 means it doesn't think it's shaded at all.

Now, if that returns a positive value, then try running the second routine, PlaceXinShadedCells(), while one of the shaded cells is still selected.  See if that works worth a darn.  And if it doesn't, at least you haven't lost anything since you should be using a copy of the workbook (or make a copy and try all of this in the original).

Sub FindInteriorColorCode()

'choose a cell and then run this code

'from [View] {Macros}

MsgBox "Cell " & ActiveCell.Address & " interior Color Index is " _

 & ActiveCell.Interior.ColorIndex

'-4142 is no shading/color

'3 is standard red

'Excel 2010 dark red is 18

End Sub

Sub PlaceXinShadedCells()

  Dim myColorIndex As Integer

  Dim anyCell As Range

'select one "sample" cell before running this macro

  myColorIndex = ActiveCell.Interior.ColorIndex

  For Each anyCell In ActiveSheet.UsedRange

    If anyCell.Interior.ColorIndex = myColorIndex Then

      'only place x in cell if it is empty

      If IsEmpty(anyCell) Then

        anyCell.Value = "x"

      End If

    End If

  Next

  MsgBox "Task completed"

End Sub

Answer 3

Hi,

Try to select the colour manually instead of "choose format from cell".

Could you upload the file somewhere and then share a link here.

Answer 4

I suspect that your client may be using Conditional Formatting on those cells - conditional formatting doesn't "appear" like regular formatting for the Find and Replace option.

Typically to find out what color (or other formatting) is being applied by conditional formatting requires a macro and you usually write it to test the value of that cell or whatever other cell(s) control the conditional formatting to make an intelligent guess as to what color must be applied based on those other conditions.

To test my theory, open the workbook from the client and choose one of the cells and click the Conditional Format drop-down on the [Home] tab and then choose Manage rules.  If the dialog that opens up has rules listed in it for that cell, then it pretty much confirms that the cell's color is being controlled by conditional formatting.

Find out what the rule is that turns it red, post it here and I'll bet myself or someone else can come up with a macro to handle this for you.

Answer 5

Followed your instructions, and there are no rules under "Conditional Formatting Rules Manager"