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


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.


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

Color = Selection.Interior.ColorIndex
Selection.Style = "Normal"
Selection.Interior.ColorIndex = Color

End Sub

Hope this will work for you.


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").Interior.ColorIndex = iClr

End Sub


OzGrid Business Applications