Remove conditional formatting via macro BUT leave the format

[kwagner1]

I am currently using the following code to conditionally format cell properties (i.e. Bold, color based on "=today(-7) ).
MY PROBLEM: if i conditionally format my spreadsheet today and and then reopen the file a month from now, the formatting of the cells will be lost...
MY QUESTION: How can i conditionally format the cells based on the prior 7 days and then, before my other macro code saves the file, remove the conditional formatting BUT leave the values and formats in place? (hope that makes sense)
Basically i want to copy-pasteSpecial the values and formats and remove the conditional formattting.

Here's my current code:
Sub FormatNewSubmits()
' this macro will conditionally format cells based on the date in the cell. If a
' date is within 7 days of running this macro the cell will be formatted.
Range("B1").Select
With Range("B2:B" & Range("B65536").End(xlUp).Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:= _
"=TODAY()-7"
.FormatConditions(1).Interior.ColorIndex = 35 '
.FormatConditions(1).Font.Bold = True
.FormatConditions(1).Font.ColorIndex = 55 '
End With

End Sub

[Andrew Poulsom]

Try this:

Sub FixFormats()
    Dim c As Range
    For Each c In Range("B2:B" & Range("B65536").End(xlUp).Row)
        With c.FormatConditions(1)
            If c.Value > Evaluate(.Formula1) Then
                c.Interior.ColorIndex = .Interior.ColorIndex
                c.Font.Bold = .Font.Bold
                c.Font.ColorIndex = .Font.ColorIndex
            End If
        End With
        c.FormatConditions.Delete
    Next c
End Sub

[kwagner1]

Beautiful! thanks.. that did it!

[kwagner1]

ahhhhhh.... more help needed... i have another column that is conditionally formated with formulas that i cant seem to get working. I can't figure out how to do what we did above for that column.... can you help?

Here is my code on that column:
Sub FormatCellsYellow()
' this macro will conditionally format a cell based on the text value in the cell. If a date text
' value of today - 6 days is found (mm/dd format) the cell will be shaded light yellow - "36".
Range("I1").Select
With Range("I1:I" & Range("I65536").End(xlUp).Row)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(ISNUMBER(FIND(TEXT(TODAY(),""mm/dd""),I1)),ISNUMBER(FIND(TEXT(TODAY()-1,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(TODAY()-2,""mm/dd""),I1)))"
.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=OR(ISNUMBER(FIND(TEXT(TODAY()-3,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(TODAY()-4,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(TODAY()-5,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(TODAY()-6,""mm/dd""),I1)))"
.FormatConditions(2).Interior.ColorIndex = 36
End With
End Sub

[Andrew Poulsom]

I would suggest using a date variable in your code instead of the TODAY() function. Then you won't have to do any conversion.

Sub FormatCellsYellow()
' this macro will conditionally format a cell based on the text value in the cell. If a date text
' value of today - 6 days is found (mm/dd format) the cell will be shaded light yellow - "36".
    Dim MyDate As Long
    MyDate = Date
    Range("I1").Select
    With Range("I1:I" & Range("I65536").End(xlUp).Row)
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(ISNUMBER(FIND(TEXT(" & MyDate & ",""mm/dd""),I1)),ISNUMBER(FIND(TEXT(" & MyDate & "-1,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(" & MyDate & "-2,""mm/dd""),I1)))"
        .FormatConditions(1).Interior.ColorIndex = 36
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=OR(ISNUMBER(FIND(TEXT(" & MyDate & "-3,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(" & MyDate & "-4,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(" & MyDate & "-5,""mm/dd""),I1)),ISNUMBER(FIND(TEXT(" & MyDate & "-6,""mm/dd""),I1)))"
        .FormatConditions(2).Interior.ColorIndex = 36
    End With
End Sub

[kwagner1]

Great! i think that will do it.... (i had the same thought on my way into work this morning..... guess i needed a little sleep....)

Have a good one!