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
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