conditional formating

mazharacca

New Member
Joined
Jun 22, 2017
Messages
7
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
1591184889517.png
 

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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
AB
1CLTDcontain LTD
2D
3E
4F
5G
6H
7J
8PVTcontain PVT
9Q
10R
11w
12
13S
14
15T
16U
17V
18CLTDcontain LTD
19D
20
21Z
22AA
23DD
24PVTcontain PVT
25RR
Sheet4
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:A25Expression=Test1textNO
 

mazharacca

New Member
Joined
Jun 22, 2017
Messages
7

ADVERTISEMENT

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

shaowu459

Well-known Member
Joined
Apr 26, 2018
Messages
514
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,887
Messages
5,574,839
Members
412,620
Latest member
sharma7s
Top