Remove fill color for cells without text only

[Guest]

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!

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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

Thanks, Gary"s Student!

 

[Guest]

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

 

[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

 

[Gord Dibben]

For Each r in ActiveSheet.Range("G2:G477")


Gord Dibben MS Excel MVP

 

[Studebaker]

Thank you, Gord Dibben!

For Each r in ActiveSheet.Range("G2:G477")


Gord Dibben MS Excel MVP