conditional formating

[mazharacca]

My data contained in column A, I want to apply conditional formatting on the condition that if a cell contains word "LTD" OR "PVT" then format up to 5 non blank cells below the cells which meet the above mentioned criteria.

 

[shaowu459]

I'm not quite clear about your question. Do you need something like below picture?
A23 should also be highlighted because it is the 5th non blank cell under A17

 

[mazharacca]

Yes Exactly i need this as shown above

 

[shaowu459]

for your reference:
Define a name Test1=COUNTIF(INDIRECT("A"&LOOKUP(1,0/MMULT(IFERROR(FIND({"LTD","PVT"},$A$1:$A1),),{1;1}),ROW($1:1))&":A"&ROW()),"*")<7

MrExcel.xlsx
	A	B
1	CLTD	contain LTD
2	D
3	E
4	F
5	G
6	H
7	J
8	PVT	contain PVT
9	Q
10	R
11	w
12
13	S
14
15	T
16	U
17	V
18	CLTD	contain LTD
19	D
20
21	Z
22	AA
23	DD
24	PVT	contain PVT
25	RR
Sheet4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A25	Expression	=Test1	text	NO

 

[mazharacca]

Thank you for reply
Formula you have given above doesn't work correctly, please explain in detail

 

[mazharacca]

attachment

 

[shaowu459]

When define the name, just paste =COUNTIF(INDIRECT("A"&LOOKUP(1,0/MMULT(IFERROR(FIND({"LTD","PVT"},$A$1:$A1),),{1;1}),ROW($1:1))&":A"&ROW()),"*")<7
In the "refer to" textbox.

 

[shaowu459]