Conditional Formatting Code in Macros

[LBryant777]

I submitted a question pertaining to conditional formatting that was answered in this thread:
thread68-819125. When I do this manually, it works like charm. However, when I try to incorporate this method into an existing macro, it does nothing. Here is the code it is a part of (inserted code in BOLD:

Sub New_Report_Copy()
'
' New_Report_Copy Macro
' Macro recorded 10/30/2003
'
    NewDate = InputBox("Enter today's date")
    
    Rows("1:1").EntireRow.Select
    Selection.Delete
    Selection.CurrentRegion.Select
    Selection.copy
    Sheets("Detailed Report").Select
    Range("A2").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("K1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=11, Criteria1:="#VALUE!"
    Range("K4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range(Selection, Selection.End(xlUp)).Select
    Selection.AutoFilter Field:=11
    Selection.AutoFilter
    Range("A1").Select
    Selection.Sort Key1:=ActiveCell.Offset(0, 7).Range("A1"), Order1:= _
        xlAscending, Key2:=ActiveCell.Offset(0, 9).Range("A1"), Order2:=xlAscending _
        , Key3:=ActiveCell.Offset(0, 10).Range("A1"), Order1:=xlAscending, _
        Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:= _
        xlTopToBottom
    Sheets("Timeline").Select
    Range("E1").Select
    ActiveCell.FormulaR1C1 = NewDate
    Selection.Sort Key1:=Range("C2"), Order1:=xlAscending, Key2:=Range("D2") _
        , Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
[b]
    Range("E2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(COUNTIF(E$2:E$999,""P"")>1,E2=""P"")"
    Selection.FormatConditions(1).Font.ColorIndex = 3
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(1).Interior.ColorIndex = 3
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(COUNTIF(E$2:E$999,""P"")=1,E2=""P"")"
    Selection.FormatConditions(2).Font.ColorIndex = 10
    With Selection.FormatConditions(2).Borders(xlTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(2).Borders(xlBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .Weight = xlThin
        .ColorIndex = xlAutomatic
    End With
    Selection.FormatConditions(2).Interior.ColorIndex = 10[/b]

    Sheets(Array("ccs_new", "ccs_compare")).Select
    Sheets("ccs_new").Activate
    ActiveWindow.SelectedSheets.Delete
'
End Sub

Should it be formatted differently because it is in VBA? Please advise.

 

[TonyJollans]

Hi LBryant777,

It works for me. Do you really mean it does NOTHING at all?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[LBryant777]

I mean the conditional formatting does not apply itself. However, when I step through the macro, it works - am I missing something or should I run this portion of code as a separate macro?

 

[TonyJollans]

Hi LBryant777,

There is nothing special you need to do. Are you sure your conditions are met?

When I did it (with a small range of E2:H5), I put a "P" in E2 and no others in column E and it turned green; I then put a "P" in E4 and both E2 and E4 turned red. The same worked in column F.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.