Clear cell based on shading
 

Hi, I'm brand new to the forum, and very inexperienced with vba, but...
I have a large spreadsheet, and every cell is linked to another file
Many of these cells are grayed out, but the problem is that because o
the linking, I have many cells with an annoying '0' in them.

I only have to clear these cells once ever, but there are SO many o
them I thought maybe a macro would make quick work of it.

I need to clear the values of all the cells on my spreadsheet thats ar
shaded a specific color. How do I do this

--
demon4
-----------------------------------------------------------------------
demon42's Profile: http://www.excelforum.com/member.php...fo&userid=2744
View this thread: http://www.excelforum.com/showthread.php?threadid=46965

Clear cell based on shading
 

First, there are lots of shades of grey.

One way to find out the one you're working with is to select one of those cells.

Then hit alt-f8 (to get to the vbe)
hit ctrl-g (to see the immediate window)
then type this and hit enter:
msgbox activecell.interior.Colorindex

Note the number you get back.

Then change this macro to use that number:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
For Each myCell In myRng.Cells
If myCell.Interior.ColorIndex = 6 Then
myCell.ClearContents
End If
Next myCell

End Sub

I used 6 for my test--it was a dark yellow.

Then select your range to fix and run this macro.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

demon42 wrote:

Hi, I'm brand new to the forum, and very inexperienced with vba, but...
I have a large spreadsheet, and every cell is linked to another file.
Many of these cells are grayed out, but the problem is that because of
the linking, I have many cells with an annoying '0' in them.

I only have to clear these cells once ever, but there are SO many of
them I thought maybe a macro would make quick work of it.

I need to clear the values of all the cells on my spreadsheet thats are
shaded a specific color. How do I do this?

--
demon42
------------------------------------------------------------------------
demon42's Profile: http://www.excelforum.com/member.php...o&userid=27448
View this thread: http://www.excelforum.com/showthread...hreadid=469659

--

Dave Peterson