Macro to erase all data and formating in a named range except background color.

Posted by Nona Sloven on May 18, 2001 10:01 AM

Hello:

My named range is named GreenRange. I need a simple macro to erase all data and formating in all the cells in the named range except the backgound color. It should get rid of borders, bold type fonts should be set to regular fonts, and any data in the range. I know this macro is simple but I have not been able to work around. Thank you in advance.

Nona

Posted by Barrie Davidson on May 18, 2001 10:57 AM

Hi Nona, this will reset your range to the normal format and then change the range to its original color.

Sub Erase_Format()
' Written by Barrie Davidson
Dim Color

Range("GreenRange").Select
Color = Selection.Interior.ColorIndex
Selection.Style = "Normal"
Selection.Interior.ColorIndex = Color

End Sub

Hope this will work for you.

Barrie

Posted by Dave Hawley on May 18, 2001 2:32 PM

Here's another way:

Sub DoIt()
Dim iClr As Integer

iClr = Range("GreenRange").Interior.ColorIndex
Range("GreenRange").Clear
Range("GreenRange").Interior.ColorIndex = iClr

End Sub

Dave

OzGrid Business Applications