New Contributor

# Need to highlight a cell if 3 conditions are correct

I need to highlight a cell if 3 certain factors are true.

I have tried the IF(AND(A2:A80="max", B2:B80>=30, C2:C3="Level")) and it does not work.

Can anyone help me.

I know how to use excel but am FAR from an expert.

7 Replies

# Re: Need to highlight a cell if 3 conditions are correct

1) You don't need IF here.

2) The formula =AND(A2:A80="max", B2:B80>=30, C2:C3="Level") will return TRUE if ALL of the cells A2:A80 equal "max" and ALL of the cells B2:B80 are greater than or equal to 30 and ALL of the cells C2:C3 equal "Level". Is that really what you intended?

# Re: Need to highlight a cell if 3 conditions are correct

=AND(H1:H8998="maximum", L1:L8998>=30,P1:P8998="Level")
This did not work. What have I done wrong?

# Re: Need to highlight a cell if 3 conditions are correct

Do you want to highlight a cell in row 1 if H1 ="maximum" and L1>=30 and P1="Level", and similar for row 2 etc.? If so, make sure that the active cell in the selection is in row 1 when you create the rule, and use the formula

=AND(\$H1="maximum",\$L1>=30,\$P1="Level")

# Re: Need to highlight a cell if 3 conditions are correct

=AND(\$H1="maximum",\$L1>=30,\$P1="Level 1")
This did highlight some of the cells, but not the correct ones.

# Re: Need to highlight a cell if 3 conditions are correct

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

# Re: Need to highlight a cell if 3 conditions are correct

 Last Risk Last OV #Days Last OV Date HV Days Last Drug Test Drug Test W/I 1yr Last DT + Sex Offender Medium 4/28/2023 11 4/25/23 14 7/12/22 Yes No Maximum 1/26/2023 103 5/8/23 1 11/29/22 Yes Yes Level 1 Minimum 3/30/2023 40 1/24/23 105 12/8/22 Yes No Level 1 Medium 3/31/2023 39 4/25/23 14 11/17/22 Yes No Level 1 Medium 4/11/2023 28 4/25/23 14 8/19/22 Yes Yes Level 1 Medium 4/18/2023 21 4/25/23 14 11/17/22 Yes No Level 1 Minimum 5/25/2016 2540 4/25/23 14 5/25/16 No No Level 1 Minimum 4/18/2023 21 12/15/22 145 4/18/23 Yes No Level 1 Minimum 3/6/2023 64 12/15/22 145 3/6/23 Yes No Level 1 Medium 4/19/2023 20 4/20/23 19 4/19/23 Yes Yes Level 1 Maximum 2/7/2023 91 4/7/23 32 11/30/22 Yes Yes Level 1 Minimum 2/9/2023 89 3/30/22 405 11/18/22 Yes No Level 1 Minimum 5/4/2023 5 3/14/23 56 4/5/23 Yes No Level 1 Medium 5/5/2023 4 3/14/23 56 4/28/23 Yes Yes Level 1 Medium 10/2/2019 1315 9/5/19 1,342 10/2/19 No No Level 2 Medium 4/19/2023 20 1/24/23 105 NA NA NA Level 2 Minimum 4/19/2023 20 1/13/23 116 1/18/23 Yes No Level 2

# Re: Need to highlight a cell if 3 conditions are correct

Your previous reply mentioned columns A, L and P. Which column in your sample data corresponds to L?