# Condition using formula

#### Dee Dee

##### New Member
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
ABCDEFGHLT
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
23 1DeCA-0001-2020Captain AhabdogApplicant6/30/20NY
3335 DeCA-0002-2020George JeffersondopeApplicant10/2/20NN
41861 DeCA-0003-2020James Brownbottle 10/28/20NN
5176-62 DeCA-0003-2021Jane DoewarApplicant6/27/20NN
625-94 DeCA-0003-2022JJ WalkermatApplicant5/26/20NN
7239-65 DeCA-0003-2023Louise Jeffersoncan 4/25/20YN
8271-157 DeCA-0003-2024Lucille Ballcare 3/24/20NN
9119 55DeCA-0003-2025Michael JordanshoeApplicant8/23/20YY
10120-6 DeCA-0003-2026Mickey MouseslipperApplicant8/22/20N
11121 53  8/21/20NY
12      YY
Counselings
Cell Formulas
RangeFormula
A2:A12A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B12B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C12C2=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:G12F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
H12H12=IFERROR(VLOOKUP(D12,Contacts!A:F,6,),"")
Named Ranges
NameRefers ToCells
Contacts!_FilterDatabase=Contacts!\$A\$1:\$AE\$1H12
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A1000Expression=AND(OR(\$L1="y",\$T1="Y"),\$A1<15)textNO
Cells with Data Validation
CellAllowCriteria
G1,G562:G1048576List=DropDown!\$A\$2:\$A\$5
L1:L12List=DropDown!\$C\$2:\$C\$3

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Fluff

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

#### Dee Dee

##### New Member
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

##### MrExcel MVP, Moderator
Why would A3 be coloured, both L3 & T3 are N?

#### Dee Dee

##### New Member

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

##### MrExcel MVP, Moderator
Do you want the whole row formatted or just col A?

#### Dossfm0q

##### Banned User

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

Book1
ABCDEFGHLTX
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
29 1DeCA-0001-2020Captain Ahab  6/30/2020NY7/9/2020
3-535 DeCA-0002-2020George Jefferson  10/2/2020NN
4-3161 DeCA-0003-2020James Brown  10/28/2020NN
592-62 DeCA-0003-2021Jane Doe  6/27/2020NN
6124-94 DeCA-0003-2022JJ Walker  5/26/2020NN
7155-65 DeCA-0003-2023Louise Jefferson  4/25/2020YN
Sheet5
Cell Formulas
RangeFormula
A2:A7A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B7B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C7C2=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:G7F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A1000Expression=AND(OR(\$L\$2="Y",\$T\$2="Y"),\$A\$2<15)textNO

#### Dossfm0q

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

VBA Code:
``````Sub Condition_using_formula()

Dim Rng As Range
Columns("A:A").FormatConditions.Delete
For Each Rng In ActiveSheet.Range("A2:A1000")
With Rng
"=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
End With
.FormatConditions(1).StopIfTrue = False
End With
Next
End Sub``````

Book1
ABCDEFGHLT
1Days OpenCounseling Days RemainingADR Days RemainingiComplaints/Case #Aggrieved Person (Name)COMMISSARY/HQEmployment StatusDate of Initial ContactCounseling Extension (Y/N)ADR Selected (Y/N)?
29 1DeCA-0001-2020Captain Ahab  6/30/2020NY
3435 DeCA-0002-2020George Jefferson  10/2/2020NN
4-3161 DeCA-0003-2020James Brown  10/28/2020NN
592-62 DeCA-0003-2021Jane Doe  6/27/2020NN
6124-94 DeCA-0003-2022JJ Walker  5/26/2020NN
714 -65DeCA-0003-2023Louise Jefferson  4/25/2020YY
8187-157 DeCA-0003-2024Lucille Ball  3/24/2020NN
910 55DeCA-0003-2025Michael Jordan  8/23/2020YY
Sheet5
Cell Formulas
RangeFormula
A2,A8,A4:A6A2=IFERROR(IF(X2="",TODAY()-H2,X2-H2),"")
B2:B9B2=IFERROR(IF(T2="Y","",IF(OR(L2="",L2="N"),SUM((SUM(H2+30)-TODAY())),SUM((SUM(H2+90)-TODAY())))),"")
C2:C9C2=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:G9F2=IFERROR(VLOOKUP(E2,Contacts!B:N,3),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A9Expression=AND(OR(\$L\$9="Y",\$T\$9="Y"),\$A\$9<15)textNO
A8Expression=AND(OR(\$L\$8="Y",\$T\$8="Y"),\$A\$8<15)textNO
A7Expression=AND(OR(\$L\$7="Y",\$T\$7="Y"),\$A\$7<15)textNO
A6Expression=AND(OR(\$L\$6="Y",\$T\$6="Y"),\$A\$6<15)textNO
A5Expression=AND(OR(\$L\$5="Y",\$T\$5="Y"),\$A\$5<15)textNO
A4Expression=AND(OR(\$L\$4="Y",\$T\$4="Y"),\$A\$4<15)textNO
A3Expression=AND(OR(\$L\$3="Y",\$T\$3="Y"),\$A\$3<15)textNO
A2Expression=AND(OR(\$L\$2="Y",\$T\$2="Y"),\$A\$2<15)textNO

#### Fluff

##### MrExcel MVP, Moderator
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.

Replies
0
Views
99
Replies
4
Views
355
Replies
16
Views
353
Replies
1
Views
115
Replies
3
Views
674

1,127,719
Messages
5,626,469
Members
416,187
Latest member
L_D18

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back