0

I have an excel table, there are values in the cells, some of these cells have colored background, some don't have any background color. And some of these cells (colored or not) have the same values.

When I try to delete the contents of non-colored cells using "Find and Replace", it also deletes the colored cells with the same value. Is there a way to achieve this?

I just want to erase certain values only in non-colored cells.

Example file

Improve this question
4
  • Try using .ClearContents Commented Jul 31, 2015 at 8:02
  • A good solution to find vba code is to record a macro. Start the record, do your clear manually (reset color, reset border, etc...), stop the record and take a look what Excel does in vba. After that, you juste have to modify it depending your needs Commented Jul 31, 2015 at 8:18
  • There are different values. I have to create a different macro for every one of them? Commented Jul 31, 2015 at 8:39
  • I need to mention that it is not some rows or columns that are colored but certain cells randomly located on the table. Commented Jul 31, 2015 at 8:43

2 Answers 2

Reset to default
1

CTRL + H

Enter the value to delete in the 'find'. Specifically enter "FORMAT" -> "WHITE FILL". Instead of saying "NO VALUES" in the FORMAT field, it should now say "PREVIEW".

Then in the REPLACE field, type nothing. Leave the FORMAT field as "NO VALUES".

Replace all, and done.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I need to keep the background colors of the colored cells. This method makes it all white.
@Max could you clarify - assume you have a value "5" in a coloured cell, and "5" in a white cell? Which do you want to delete? If you set the "replace with" to have "NO VALUES" (ie: it will ignore formatting), it should not change the colour of the cells which have their content changed.
yes, it worked. thanks. I still find this strange, there should be another way of doing this. thanks.
0

Try using .Interior.ColorIndex = None

So possibly something like:

If Cell(row, col).Interior.ColorIndex = None Then
    'Do FindAndReplace action here 
    '(Or try .ClearContents, as Omar suggested as a comment to your question)
End If

and adding a loop to get through your range.

It's difficult to help more than that without seeing what you're already tried, so if you could show what you've already done maybe others could help you better in your task?

Improve this answer

1 Comment

I have only tried "Find and Replace" function. I am not familiar with Vba. I added an example file to the question. The real table is much bigger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.