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!

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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




--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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

Thanks, Gary"s Student!

"Gary''s Student" wrote:


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




--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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

"Studebaker" wrote:

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

Thanks, Gary"s Student!

"Gary''s Student" wrote:


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




--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

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


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 15:16:01 -0800, Studebaker
wrote:

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!

Thank you, Gord Dibben!

"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP

On Mon, 26 Nov 2007 15:16:01 -0800, Studebaker
wrote:

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

"Gary''s Student" wrote:

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



--
Gary''s Student - gsnu200755


"Studebaker" wrote:

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!