VBA Macro To Clear Everything Excel Formula And Bold Text

[Flupsie]

I would like a macro to clear data on all sheets except formula, text, items in bold, values to the right of "GP % target" (the text appears in Col A and the values in Col B:M) and data that has been color filled.

I have the following macro and have attached a sample workbook. I have also posted on Ozgrid, but the final solution I received is not working and I have not received any further reply


Clear Data on all sheets except last 20 sheets

In the sample data, only the first and last sheets data not to be cleared

Your assistance in resolving this is most appreciated

 Sub Clear_Data() 
    Dim i As Integer, r As Range 
    On Error Resume Next 
    With CreateObject("VBScript.RegExp") 
        .Pattern = "^=-?\d+(\.\d+)?([\+-/\\^\*]\d+(\.\d+)?)?$" 
        For i = 1 To Sheets.Count - 1
            For Each r In Sheets(i).UsedRange.SpecialCells(xlCellTypeFormulas) 
                If .test(r.Formula) Then r.ClearContents 
            Next 
            For Each r In Sheets(i).UsedRange.SpecialCells(xlCellTypeConstants, 1) 
                If Sheets(i).Cells(r.Row, 1).Value <> "GP % target" Then r.ClearContents 
            Next 
        Next 
    End With 
End Sub

https://www.dropbox.com/s/bodg6xsoo9...a.GP%20%25.xls

[Excel Fox]

If you want to clear everything except formula, text, items in bold, values to the right of "GP % target" and data that has been color filled, what else is left? The ones you have marked with arrows, are either bold, or are formulas, or both!!

[Howardc]

Thanks for the Reply.

I removed this section from the code and changed GP % target to incorporate each branch for eg GP % target-Br1 , GP % target-Br2 and then set up a table and had a Vlookup in Col B and this resolved my problem

 If Sheets(i).Cells(r.Row, 1).Value <> "GP % target" Then r.ClearContents