Results 1 to 6 of 6

Remove conditional formatting via macro BUT leave the format

This is a discussion on Remove conditional formatting via macro BUT leave the format within the Excel Questions forums, part of the Question Forums category; I am currently using the following code to conditionally format cell properties (i.e. Bold, color based on "=today(-7) ). MY ...

  1. #1
    Board Regular
    Join Date
    Jun 2003
    Posts
    443

    Default Remove conditional formatting via macro BUT leave the format

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,983

    Default Re: Remove conditional formatting via macro BUT leave the fo

    Try this:

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

  3. #3
    Board Regular
    Join Date
    Jun 2003
    Posts
    443

    Default Re: Remove conditional formatting via macro BUT leave the fo

    Beautiful! thanks.. that did it!


  4. #4
    Board Regular
    Join Date
    Jun 2003
    Posts
    443

    Default Re: Remove conditional formatting via macro BUT leave the fo

    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

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,983

    Default Re: Remove conditional formatting via macro BUT leave the fo

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

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

  6. #6
    Board Regular
    Join Date
    Jun 2003
    Posts
    443

    Default Re: Remove conditional formatting via macro BUT leave the fo

    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!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com