CODEWITHSUNDEEP
excelexcel-formulaworksheet-functionconditional-formattingvba
Leah
Leah

Reputation: 11

Excel Column Conditional Formatting

I have an Excel sheet that I need to apply some conditional formatting to and am confused on how to do it.

What I need to do is make one columns values (in my case column G) red IF the date in another column (column E) is over five days old and IF there is no value in a different column (Column I).

I also need it so that the first value (in column G) reverts to the regular black if there is a value added to the second conditional column (column I).

An additional issue is that even with Conditional Formatting that meets the requirements prior to saving or running the code below, afterwards the CF does not persist:

This is our "Show Most Recent" or collapse code:

    Sub Collapse1()
'
' Collapse Macro
' Collapses rows to show only the most recent issuance of each unique Document Number
'

'
    Range("Table1[[#Headers],[Document Number]]").Select
    Cells.FormatConditions.Delete
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveSheet.ListObjects("Table1").Sort. _
        SortFields.Clear
    ActiveSheet.ListObjects("Table1").Sort. _
        SortFields.Add Key:=Range("Table1[Document Number]"), SortOn:= _
        xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.ListObjects("Table1").Sort. _
        SortFields.Add Key:=Range("Table1[Issuance" & Chr(10) & "Date]"), SortOn:=xlSortOnValues _
        , Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveSheet.ListObjects("Table1"). _
        Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("Table1[Document Number]").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTIF($A$12:A12,A12)>1"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Range("Table1[[#Headers],[Document Number]]").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:=RGB _
        (255, 255, 255), Operator:=xlFilterNoFill
End Sub

Upvotes: 1

Views: 295

Answers (1)

pnuts
pnuts

Reputation: 59495

Different interpretation. Please select Column G and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true:: 

=AND(E1<>"",E1<TODAY()-5,I1="")

Format..., select your choice of formatting (not black), OK, OK.

This triggers after five full days.

Upvotes: 0

Related Questions