Conditionally Format Row above Matched Text Criteria in Microsoft Excel 2010

 

In this article, you will learn how to format row above the matched criteria.

For example:
 
img1
 
The criterion is to look for text Subtotal in the above table & then color the row which is just above to Subtotal.

We will use a combination of ISNUMBER & SEARCH functions to get the output.

ISNUMBER: Checks whether a value is a number, and returns TRUE or FALSE.

Syntax =ISNUMBER(value)

value: It is the value that you want to check whether it is a number or not.

The SEARCH function returns the number of the character at which a specific character or text string is first found, reading left to right (not case-sensitive)

Syntax =SEARCH(find_text,within_text,start_num)

find_text: The text that you want to find.

within_text: Itis the text in which you want to search.

start_num: This is optional. It is the number in the string from which you want to extract data.
 
Select the range A2:C10

Open the New Rule Manager from Conditional Formatting or press ALT + O + D

Select “Use a formula to determine cells to format”

Enter the formula as =ISNUMBER(SEARCH(“subtotal”,$A3))
 
img2
 
Click on Format to apply color & then click on OK button twice.
 
img3
 
In this way, we can easily format the row above the matched criteria.
 
 



Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>