MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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