Condition using formula

[Dee Dee]

Hi,

I am having an issue where my formula for this condition does not seem to work right.

if the columns T OR L has "Y" AND the column A is between 0 and 15 I want to color it. Unfortunately, the number range does not seem to be working. The numbers in A are generated by a formula and I am not sure if that is what is stopping this from working.

Complaints Tracker - FY21.xlsm
	A	B	C	D	E	F	G	H				L								T
1	Days Open	Counseling Days Remaining	ADR Days Remaining	iComplaints/Case #	Aggrieved Person (Name)	COMMISSARY/HQ	Employment Status	Date of Initial Contact				Counseling Extension (Y/N)								ADR Selected (Y/N)?
2	3		1	DeCA-0001-2020	Captain Ahab	dog	Applicant	6/30/20				N								Y
3	3	35		DeCA-0002-2020	George Jefferson	dope	Applicant	10/2/20				N								N
4	18	61		DeCA-0003-2020	James Brown	bottle		10/28/20				N								N
5	176	-62		DeCA-0003-2021	Jane Doe	war	Applicant	6/27/20				N								N
6	25	-94		DeCA-0003-2022	JJ Walker	mat	Applicant	5/26/20				N								N
7	239	-65		DeCA-0003-2023	Louise Jefferson	can		4/25/20				Y								N
8	271	-157		DeCA-0003-2024	Lucille Ball	care		3/24/20				N								N
9	119		55	DeCA-0003-2025	Michael Jordan	shoe	Applicant	8/23/20				Y								Y
10	120	-6		DeCA-0003-2026	Mickey Mouse	slipper	Applicant	8/22/20												N
11	121		53					8/21/20				N								Y
12												Y								Y
Counselings

Cell Formulas
Range	Formula
A2:A12	A2	=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B12	B2	=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C12	C2	=IFERROR(IF(T2="","",IFS(AND(T2="Y",L2="N"),SUM((SUM(H2+90)-TODAY())),AND(L2="Y",T2="Y"),SUM((SUM(H2+90)-TODAY())))),"")
F2:G12	F2	=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
H12	H12	=IFERROR(VLOOKUP(D12,Contacts!A:F,6,),"")

Named Ranges
Name	Refers To	Cells
Contacts!_FilterDatabase	=Contacts!$A$1:$AE$1	H12

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1000	Expression	=AND(OR($L1="y",$T1="Y"),$A1<15)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
G1,G562:G1048576	List	=DropDown!$A$2:$A$5
L1:L12	List	=DropDown!$C$2:$C$3

 

[Fluff]

In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.

 

[Dee Dee]

In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.

Does my formula specifically ask about that one cell alone? I was intending on making the condition for the whole row, in which case A3 should be colored as well.

 

[Fluff]

Why would A3 be coloured, both L3 & T3 are N?

 

[Dee Dee]

Does my formula specifically ask about that one cell alone? I was intending on making the condition for the whole row, in which case A3 should be colored as well.

In that data only A2 fits your criteria & it has been coloured, so not sure what your problem is.

Nevermind. I see it. Thanks a bunch!

 

[Fluff]

Do you want the whole row formatted or just col A?

 

[Dossfm0q]

Check "$" =AND(OR($L$2="Y",$T$2="Y"),$A$2<15)

Book1
	A	B	C	D	E	F	G	H				L								T				X
1	Days Open	Counseling Days Remaining	ADR Days Remaining	iComplaints/Case #	Aggrieved Person (Name)	COMMISSARY/HQ	Employment Status	Date of Initial Contact				Counseling Extension (Y/N)								ADR Selected (Y/N)?
2	9		1	DeCA-0001-2020	Captain Ahab			6/30/2020				N								Y				7/9/2020
3	-5	35		DeCA-0002-2020	George Jefferson			10/2/2020				N								N
4	-31	61		DeCA-0003-2020	James Brown			10/28/2020				N								N
5	92	-62		DeCA-0003-2021	Jane Doe			6/27/2020				N								N
6	124	-94		DeCA-0003-2022	JJ Walker			5/26/2020				N								N
7	155	-65		DeCA-0003-2023	Louise Jefferson			4/25/2020				Y								N
Sheet5

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B7	B2	=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C7	C2	=IFERROR(IF(T2="","",IFS(AND(T2="Y",L2="N"),SUM((SUM(H2+90)-TODAY())),AND(L2="Y",T2="Y"),SUM((SUM(H2+90)-TODAY())))),"")
F2:G7	F2	=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A1000	Expression	=AND(OR($L$2="Y",$T$2="Y"),$A$2<15)	text	NO

 

[Dossfm0q]

as you Like if you need to apply it for Individual Cell In Col "A" With the same Row in col "T" & "L"

Sub Condition_using_formula()
   
    Dim Rng As Range
    Columns("A:A").FormatConditions.Delete
    For Each Rng In ActiveSheet.Range("A2:A1000")
    With Rng
        .FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(OR($L$" & Rng.Row & "=""Y"",$T$" & Rng.Row & "=""Y""),$A$" & Rng.Row & "<15)"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
        End With
    .FormatConditions(1).StopIfTrue = False
    End With
    Next
End Sub

Book1
	A	B	C	D	E	F	G	H				L								T
1	Days Open	Counseling Days Remaining	ADR Days Remaining	iComplaints/Case #	Aggrieved Person (Name)	COMMISSARY/HQ	Employment Status	Date of Initial Contact				Counseling Extension (Y/N)								ADR Selected (Y/N)?
2	9		1	DeCA-0001-2020	Captain Ahab			6/30/2020				N								Y
3	4	35		DeCA-0002-2020	George Jefferson			10/2/2020				N								N
4	-31	61		DeCA-0003-2020	James Brown			10/28/2020				N								N
5	92	-62		DeCA-0003-2021	Jane Doe			6/27/2020				N								N
6	124	-94		DeCA-0003-2022	JJ Walker			5/26/2020				N								N
7	14		-65	DeCA-0003-2023	Louise Jefferson			4/25/2020				Y								Y
8	187	-157		DeCA-0003-2024	Lucille Ball			3/24/2020				N								N
9	10		55	DeCA-0003-2025	Michael Jordan			8/23/2020				Y								Y
Sheet5

Cell Formulas
Range	Formula
A2,A8,A4:A6	A2	=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B9	B2	=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C9	C2	=IFERROR(IF(T2="","",IFS(AND(T2="Y",L2="N"),SUM((SUM(H2+90)-TODAY())),AND(L2="Y",T2="Y"),SUM((SUM(H2+90)-TODAY())))),"")
F2:G9	F2	=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9	Expression	=AND(OR($L$9="Y",$T$9="Y"),$A$9<15)	text	NO
A8	Expression	=AND(OR($L$8="Y",$T$8="Y"),$A$8<15)	text	NO
A7	Expression	=AND(OR($L$7="Y",$T$7="Y"),$A$7<15)	text	NO
A6	Expression	=AND(OR($L$6="Y",$T$6="Y"),$A$6<15)	text	NO
A5	Expression	=AND(OR($L$5="Y",$T$5="Y"),$A$5<15)	text	NO
A4	Expression	=AND(OR($L$4="Y",$T$4="Y"),$A$4<15)	text	NO
A3	Expression	=AND(OR($L$3="Y",$T$3="Y"),$A$3<15)	text	NO
A2	Expression	=AND(OR($L$2="Y",$T$2="Y"),$A$2<15)	text	NO

 

[Fluff]

Why on earth are you suggesting setting 999 individual CF rules, when you can use just one?
Namely the rule that the OP is already using.