Need to highlight a cell if 3 conditions are correct

Copper Contributor

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


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?

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


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 1")
This did highlight some of the cells, but not the correct ones.


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?

Last RiskLast OV#Days Last OVDate HVDaysLast Drug TestDrug Test W/I 1yrLast DT +Sex Offender
Maximum1/26/20231035/8/23111/29/22YesYesLevel 1
Minimum3/30/2023401/24/2310512/8/22YesNoLevel 1
Medium3/31/2023394/25/231411/17/22YesNoLevel 1
Medium4/11/2023284/25/23148/19/22YesYesLevel 1
Medium4/18/2023214/25/231411/17/22YesNoLevel 1
Minimum5/25/201625404/25/23145/25/16NoNoLevel 1
Minimum4/18/20232112/15/221454/18/23YesNoLevel 1
Minimum3/6/20236412/15/221453/6/23YesNoLevel 1
Medium4/19/2023204/20/23194/19/23YesYesLevel 1
Maximum2/7/2023914/7/233211/30/22YesYesLevel 1
Minimum2/9/2023893/30/2240511/18/22YesNoLevel 1
Minimum5/4/202353/14/23564/5/23YesNoLevel 1
Medium5/5/202343/14/23564/28/23YesYesLevel 1
Medium10/2/201913159/5/191,34210/2/19NoNoLevel 2
Medium4/19/2023201/24/23105NANANALevel 2
Minimum4/19/2023201/13/231161/18/23YesNoLevel 2


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