Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!

Subscribe for Free Excel tips & more!

Free Excel Forum

Formula To Highlight An Entire Row When Certain Text Is Present.

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is there any formula or condition I can use that will look within a range and
highlight the entire row when specific text is contained in the row?

View Answers     

Similar Excel Tutorials

Highlight Rows that Meet a Certain Condition in Excel
In this tutorial I am going to cover how to highlight rows that meet a certain condition. To do this I use the Cond ...
Prevent Spaces from Being Entered in Text in Excel
How to prevent a user from entering any spaces within text in Excel.  This allows you to keep data clean when a use ...
Automatically Shade Every Other Row When You Add Data in Excel
How to make Excel automatically add row shading to every other row when you add data to the spreadsheet. This does ...
PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...

Helpful Excel Macros

Highlight Cells which Contain Formulas
- This macro will highlight all of the cells in a worksheet which contain a formula. The first one listed will highlight
Highlight Cells with Text or Formulas (non-empty cells)
- This macro will highlight all cells in excel which are not empty. This means that if a cell contains formulas, text, nu
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Highlight Every Other Row in a Selection in Excel - Table Formatting
- This free Excel macro will highlight every other row in a selection of cells with a color that is specified within the m
Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to

Similar Topics

I am trying to Highlight an entire row, if a vlue exists within a column. I have read many forums in which you can create a macro to do this. I have "10" values that I would like to use as far as values within a column. I would like to run the macro, and if a number exists, I would like it to highlight the entire row.
The cells that i would like to have read are within Columns "w" and "x". The cell values are within a merged cell because of the way that the program exports the report into excel.

The values that I would like to have met are :

96- Highlight Entire Row Orange
237-Highlight Entire Row Yellow
402-Highlight Entire Row Blue
680-Highlight Entire Row Blue
871-Highlight Entire Row Blue
827-Highlight Entire Row Blue
213-Highlight Entire Row Blue
833-Highlight Entire Row Blue
339-Highlight Entire Row Blue

Please let me know If I can post the excel spreadsheet, and whether or not I can be helped. Thanks !

Hi I am a completely new to VBA and have a question.

Is there any way to create a formula that will either highlight (in red lets say) an entire row, or move the entire row to another worksheet, if one cell has "Y" in it. Moving the entire row would be preferable.

I have been able to highlight the one cell, but I need to either highlight the entire row, or move it completely, and this seems to be way beyond my capabilities.

Any help would be much appreciated.

Ok...I worded my last post wrong...What I actually need to do is highlight an entire row if one specific cell is empty. In my situation...I need to highlight my entire range of data in a row red if cell in column D is blank. Sorry for the wrong wording...any help would be appreciated...thanks.

I want highlight the entire row or particular cell.
I have columns called permit no., description, requester, date requested validity, approved and permit opened. . I want highlight the entire row or particular cell if the permit is not opened 7 days from the date of approval. Is it also possible to highlight with flashing text.

A sample excel file is attached

I searched a specific data and clicked find all. Then i proceeded to highlight all of those cell. However i would like it to highlight the entire row so that I can transfer it to a different sheet with all the information??


Hi there,

My version of excel only contains options to add 3 conditional highlights, however, I require 6 conditional highlights to ideally perform a specific task. Is there anyway of achieving the following, perhaps via a macro???

If a specific word is typed into cell AG2, the entire row 2 will be highlighted in a specific colour including cell AG2 according to this order:

paid = green highlight
sol = pink highlight
hold = red
temp = yellow highlight
gen = orange highlight
call = blue

Please note that I wish the formulae to function through the entire column AG from cell AG2 onward. Row 1 contains headers. So if, for example, a word indicated above is typed into AG3, then the entire row 3 will be highlighted with the corresponding highlight.

Your help in this matter is most appreciated.

Kind regards,



So I'm trying to write a "double" conditional formatting function. Let me explain with an example:

Row 1, Cell C1 : I used conditional formatting to highlight this cell if it contained a certain text.

Question : When this Cell C1 is highlighted automatically using conditional formatting, how do I highlight the entire Row 1?

In other words, C1 contains text which causes the conditionaly formatting function to highlight this C1, and now I that C1 is highlighted, I would like to highlight that entire Row 1.

Thanks for the help! Hope this is doable.

Hopefully simple for you but one Im struggling with!

I have a sheet with say 700 rows and 10 columns - based on the value in one cell per row I would like to highlight the whole row in red text.

For example, if cell F5 = "x" I want to highlight the entire row (cells A1:J1) in red text: if cell F5 = "y" then do nothing.


Hi all,

I'm tring to implement some code which will find and highlight cells which contain "#MULTIVALUE" in any and all tabs within a spreadsheet. The #MULTIVALUE is just plain text and not a formula generated error, if that makes any difference as the report has been pasted special values.

Is there a way to highlight an entire row, I can not use conditional formatting. The reason is I need to highlight the entire row if column H is less than or equal to .80? If I need to use VBA please provide the code.

Thank You

I have a spreadsheet with 18 columns of data, and I need to look for variances.

I need to make the entire row highlight, if one value in the row does not equal all the rest. If I can use conditional formatting, then I would like to add a second parameter that highlights the particular cell that is off.

I know that you can conditional format any unique values, but if it isn't harder I would prefer to highlight the entire row.

Another step would be that, sometimes the values change, so perhaps the first 9 columns are 7.00 and the next 9 are 8.00, I would like it to highlight that change.

Any way to do this?

I'm trying to write VB to allow selecting a phrase and a range and then
highlighting each instance of the phrase with bold and red font. Want to
highlight only the characters contained in the phrase, not the entire cell's
contents. Having trouble using a command similar to FIND in Excel, which
doesn't seem to translate well to VB. Every reference I've found is for
locating a cell, not a character within a cell.

I'm using RefEdit to select the range and a text box to enter the phrase.
Want to create a UserForm that can be added to the normal template.

Any suggestions?


I am wondering if it is possible to conditionally format any number of cells (as in there is no specific cell for the data to be entered) based on only partial text within a cell.

For example, if I want highlight a cell that contains Canada, but the text within the cell actually reads "Vancouver, Canada", will I be able to turn the text red?

I have a list of 12 countries that this needs to be done for, if possible. I am already using one conditional format for the entire worksheet that turns any cell that contains the words "At Sea" blue, but am hoping to highlight specific countries, even if the destination city is different (but still the same country, if that makes sense).

Is there a formula that would work? Something like: If "any cell" contains Canada, France, Australia, then text = red. Obviously that's a simplified version!!

Any help would be appreciated.

Many thanks

Hi All,
I need to highlight a ROW in a spreadsheet certain colors if the value of a specific cell in that ROW is equal to a specific value. See below...

If A2=100%, highlight ROW2 red
If A2=90%, highlight ROW2 green
If A2=60%, highlight ROW2 blue

....And so on for 40%, 20%, 10% and 0%.

Assitance is greatly appreciated. Thanks!!

Hi There,

I have a spreadsheet which lists Part Numbers and how many weeks away thety are from their due date. However, I am trying to conditionally format the rows that that if;

"Arrears" is in any row in column F = Highlight Red

"Weeks 14 - 17" is in any row in column F = Highlight Blue

"Weeks 18 - 22" is in any row in column F = Highlight Yellow

"Weeks 23 - 26" is in any row in column F = Highlight Green

I have tried to get just one condition working (=$F5="Weeks 14 - 17") within the attached spreadsheet but cant seem to get it working. Can anybody help?



I have a chart that shows duration over the past 20 years plotted. I want to highlight/shade a specific area/region and can't figure out how to do it. I can highlight the entire plot area, but not a specific area. I tried adding a transparent text box and placed on top, but when I copy the graph elsewhere the object doesn't go with it.

The range is top .75 and bottom -.75 I want the range .45 to -.45 highlighted


I can only seem to find info on this where there is a specific date range or they only want to highlight an individual cell.

I currently have this as my formula which highlights the entire row if the date in cell "T" is exactly 14 days from today's date, but not between, i.e. today it would highlight only dates that have 6/30/2011. I need it to highlight the entire row if the date falls between TODAY() and 14 days from today.


I know it's close, just need to know where/what to add to it.



I have a spreadsheet with one column. For example, it contains


I want to highlight the A's and don't highlight the B's and then highlight the C's and then don't highlight the D's and so on. I want to highlight or not highlight the entire row. How can I do that?

Can anybody please help me make a formula using this condition:

If the text EOSP or SOSP followed by any word appears in column A8 to A99 then highlight with yellow color the entire row to the right of the cell where the text EOSP or SOSP followed by any word appears.The cells to the right are in the range B8 to BU99.
******** ******************** ************************************************************************> Microsoft Excel - Book1 ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout G17 =
A B C D E F G 7 * * * * * * * 8 **A8*to*A99 **************B8*to*BU99*down * * * * * 9 EOSP*THIS * * * * * * 10 * * * * * * * 11 SOSP*THAT * * * * * * 12 * * * * * * * 13 * * * * * * * 14 EOSP*THEM * * * * * * 15 * * * * * * * 16 * * * * * * * 17 * * * * * * * Sheet1 *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

Thank you very much for any help.

I am using Excel 2007 and having some trouble with conditional formatting an entire line.

What I currently have is a formula in Column J: =IF(K9="Match","Match", " "). What I would like to do is if "Match" appears , highlight the entire row ( J : A ).
hope this makes sense.
Thanks for your help

In Excel 2007

I am trying to apply conditional formatting to an entire row based on ranges of test scores
Everything works fine highlighting a cell and using Highlight cell rules with the Between feature but I need the entire row to be highlighted if it meets the condition

90-100% = green
80-89% = yellow
65-79% = white
0-64% = red

Therefore I thought formula conditional rules would work instead
I have tried IF AND and combinations to get all the colors to show accross the appropriate rows

I seem to be forced to write formulas with each invidivual > or < condition such as
(TEST Scores start in Y2)
Range of cells to highlight row = =$A$2:$Y$14

I get close but the last conditional seem to wipe out all my other coloring - regardless of the order of the conditions
this all seems crazy just to get an entire row to display the highlighting
I don't think this is an uncommon request I have seen solutions all over the web but not applying a series of different colors based on value ranges

Anyone have a solution that is not VBA since this is for my user community who do not do VBA

Many thanks
Leslie Yambor

Hello folks,

In a file, i have a table that holds the unpaid invoices.

Based on the payment condition, which could be 30,60,90 days, i need to highlight specific fields.

For example, if payment condition is 30 then highlight cell A2
if payment condition is 60 then highlight cell A2 and B2
if payment condition is 90 then highlight cell A2 and B2 and C2

This is very helpful for me, in order to notify me when the payment period is pass.



Hello! I need a bit of help, please. I have a running spreadsheet that uses the mod to highlight alternate cells. I've set it up for the entire document under conditional formatting as =MOD(ROW(),2)=0

I have one particular column that contains a sum formula. If the sum=0 I want the text to appear clear or white (or ideally be deleted) so as not to appear distracting as a column of zeros with an occasional value. It just seems to make the true values harder to spot. Anyway, I added a second condition to this column that simply states if cell value=0 then format text as "white".

The rows not higlighted by the first condition work fine and the text is white, but the text in the highlighted rows remains black in this column. I tried alternating the order of the two conditions, but then the second condition cancels out the alternating highlights from the first condition.

any suggestions?

Hello all,

The subject line pretty much describes what I want to do. Ive read a ton online and for whatever reason I cant seem to get it to work. No problems getting it to highlight a single cell but I would like to get the entire row. Thanks in advance for any help you can offer. Attached is what I have so far.

For clarification for those who cant download the example right now, the cell with the date currently is set to color itself red if the date is within 30 days from now, and yellow if its between 30 and 60. I would like the entire row to highlight instead of just that cell.

I have certain things written in my A column.

I want to write conditional formatting to highlight an entire row if the A column contains the words '0-UNEXPECTED'

I've tried this... but I have no idea what i'm doing.