Remove fill color for cells without text only

S

Studebaker

Hello,

I have a huge spreadsheet where I need to remove the lavender fill color
only on the cells that have no text in them. (I want to keep the lavender
fill color in the cells that do have text.)
I tried Find/Replace but it tried to remove the fill color for every cell.

Can someone help, please?

Thank you!
 
G

Gary''s Student

try this little macro:

Sub fillout()
For Each r In ActiveSheet.UsedRange
If Application.WorksheetFunction.IsText(r.Value) Then
Else
r.Interior.ColorIndex = lxnone
End If
Next
End Sub
 
S

Studebaker

Thanks Gary's Student. I'm not too familiar with macros so bear with me.
I know I can paste this macro in the Visual Basic Editor but what do I do
then?

Do I need to go back to the spreadsheet and do something afterwards?

Thanks
 
G

Gary''s Student

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
S

Studebaker

YEAHHH!!!! It worked! You saved me a great deal of time!

Thanks, Gary"s Student!
 
S

Studebaker

Also, thanks for writing out how to deal with macros and remove them. That
really helped!
 
S

Studebaker

Hello Gary"s Student,

I've run across the same situation I need it only for column G rows 2 thru
477 (ex: G2:G477).
Can you tell me how I can type the macro so I can just remove fill color for
only cells without text for just that area of my spreadsheet?

Thank you for your help!
Studebaker
 
Top