Conditional formatting rule for remaining cells

[288enzo]

Hi all,

I'm curious to know if it's possible to create a conditional formatting rule that would be applied only if a particular cell didn't have a rule already applied?

As an example, I have a range that I'm applying two separate rules.

What I'm currently working with:

    With Range("P2:P" & lr1)
        .FormatConditions.Add Type:=xlTextString, String:="Registered", TextOperator:=xlContains
            With .FormatConditions(.FormatConditions.Count)
                 .Font.Bold = True
                 .Interior.Color = RGB(0, 153, 76)
                 .Font.Color = RGB(255, 255, 255)
                 .StopIfTrue = False
            End With
        .FormatConditions.Add Type:=xlTextString, String:="Expired", TextOperator:=xlContains
            With .FormatConditions(.FormatConditions.Count)
                .Font.Bold = True
                .Interior.Color = RGB(192, 0, 0)
                .Font.Color = RGB(255, 255, 255)
                .StopIfTrue = False
            End With
    End With

I'd like to add a third rule for all cells in the range that haven't had a rule applied to be:

            With .FormatConditions(.FormatConditions.Count)
                .Font.Bold = True
                .Interior.Color = RGB(255, 255, 0)
                .Font.Color = RGB(0, 0, 0)
                .StopIfTrue = False
            End With

 

[288enzo]

Okay, so all my remaining cells would show a day, and those are what I'd like to format. Hopefully that makes it a bit easier. I've tried "Type:=xlTimePeriod" options, but there isn't one specific to my needs. Basically, date shown greater than Today would be formatted. The issue I'm having is those cells that show Registered are also formatted when I use something like >Today().

1.xlsx
	P
7576	Registered
7577	Thursday, Nov 10
7578	Thursday, Nov 10
7579	Registered
KPI

Cell Formulas
Range	Formula
P7576:P7579	P7576	=IF(LEFT(N7576,1)="Y","Registered",IF(N7576<>"","",IF(M7576+10<TODAY(),"Expired",M7576+10)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
P2:P13671	Cell Value	contains "Expired"	text	NO
P2:P13671	Cell Value	contains "Registered"	text	NO