Sometimes, we want to highlight the row that contains the specific text. In this article, we will learn how to highlight a row, if any cell in that row contains a specific text or value. We will highlight row using conditional formatting of course.
Generic Formula in Conditional Formatting
Lookup value: it is the criteria text. It will be searched in the given range.
Lookup array: the row that you want to highlight.
The first row need to be selected while applying the conditional formatting.
Let’s see an example to make things clear.
Example: Highlight Rows That Contain a Specific Text
Here I have some data in table. Each row contains some random name. I want to highlight each row in table that contains the value written in C2. For now, in C2, I have Maya. I want to highlight each row that contains Maya in it.
Select first row of the table (A7:D7).
Now the selected row is highlighted.
Copy this range and paste special the format to the entire table.
Now each row will be highlighted that contains “Maya”. When you change the value in C2, row containing that value will be highlighted only.
How it works?
Here, we used the MATCH function of excel that returns the index of the searched value in the given range. If it fails to find the given text, it returns #NA.
In conditional formatting, any positive value is treated as TRUE and Errors are treated as FALSE. We use this functionality to highlight row.
Here our formula is =MATCH($C$2,$A7:$D7,0). Note that the lookup value’s reference is absolute. So that it always looks for value written in C2. The lookup range is A7:D7. Note that only Columns are frozen using $ and rows are left relative. It is because we want to change the lookup row when we copy the conditional formatting and while columns are absolute. In the end, we get highlighted rows that contain specific text in a cell. Learn about referencing in detail here.
If we make column relative (=MATCH($C$2,A7:D7,0)) our rows will be highlighted to first found value.
The above formula highlights the rows that contain specific text. It checks cells and matches cells value. But if you want to check the given text in between of strings then you should use this function.
Here we are just searching for text in concatenated text of A7:D7. If SEARCH finds the given text, conditional formatting treats it as TRUE else False. Rest is same as above.
Highlight Row with Case Sensitive Match
For case sensitive match, we can use FIND function. This will check text and in rows and it will highlight the row only if text and case is matched.
So yeah guys, this is the way you can highlight a row base on a text match. Let me know if you have any doubts regarding this article or any excel/vba related topic. The comments section is open for you.
The VLOOKUP Function in Excel
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.