Conditional Formatting

[th259]

If I have a formula to check if value in A1 is repeated again in column A ($A:$A=A2), if it is repeated on A3 and A4 for example, and if the first character on either Q2 or Q3 or Q4 starts with a "3" or a "4" or a "5", then I want all 3 rows to be highlighted. Is that possible? It goes all the way down to A555.

 

[Cubist]

Is this what you mean?

loop50.xlsm
	A																Q
1	A																367468
2	B																120930
3	A																5789475
4	A																477832
5	C																3454
6	C																1090
7	D																564
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A7	Expression	=(COUNTIFS(A:A,A1)>1)*(ISNUMBER(SEARCH(LEFT(Q1,1),"345")))	text	NO

 

[Cubist]

Or maybe this.

loop50.xlsm
	A																Q
1	A																367468
2	B																120930
3	A																101
4	A																102
5	C																3454
6	C																1090
7	D																364
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A7	Expression	=SUM(--(ISNUMBER(SEARCH(A1,$A$1:$A$7&"3"&$A$1:$A$7&"4"&$A$1:$A$7&"5"))))>1	text	NO

 

[th259]

Or maybe this.

loop50.xlsm
	A																Q
1	A																367468
2	B																120930
3	A																101
4	A																102
5	C																3454
6	C																1090
7	D																364
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A7	Expression	=SUM(--(ISNUMBER(SEARCH(A1,$A$1:$A$7&"3"&$A$1:$A$7&"4"&$A$1:$A$7&"5"))))>1	text	NO

Thank you for taking a look at the situation.

this one but fill the whole row (data ends on column S), and the conditional formatting needs to go to the bottom of the sheet, in this particular sheet, it goes to row 555.

 

[Cubist]

Highlight the range A1:S555 then insert this into the Conditional Formatting formula.

=SUM((COUNTIF($A:$A,$A1)>1)*($A1=$A$1:$A$555)*(ISNUMBER(SEARCH(LEFT($Q$1:$Q$555,1),"345"))))>0

loop50.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	A																301
2	A																203
3	B																345
4	C																567
5	C																123
6	D																345
7	A																101
8	E																200
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:S555	Expression	=SUM((COUNTIF($A:$A,$A1)>1)*($A1=$A$1:$A$555)*(ISNUMBER(SEARCH(LEFT($Q$1:$Q$555,1),"345"))))>0	text	YES

 

[th259]

Highlight the range A1:S555 then insert this into the Conditional Formatting formula.

=SUM((COUNTIF($A:$A,$A1)>1)*($A1=$A$1:$A$555)*(ISNUMBER(SEARCH(LEFT($Q$1:$Q$555,1),"345"))))>0

loop50.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	A																301
2	A																203
3	B																345
4	C																567
5	C																123
6	D																345
7	A																101
8	E																200
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:S555	Expression	=SUM((COUNTIF($A:$A,$A1)>1)*($A1=$A$1:$A$555)*(ISNUMBER(SEARCH(LEFT($Q$1:$Q$555,1),"345"))))>0	text	YES

this is amazing! Thank you very much!