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

Condition Format-entire Line

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

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

View Answers     

Similar Excel Tutorials

How to Add a New Line to a Message Box (MsgBox) in Excel VBA Macros
I'll show you how to create a message box popup window in Excel that contains text on multiple lines.  This allows ...
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 ...
Quickly Replace A Lot of Data in Excel
The Find & Replace works much the same way as Find and is located in the same place. (Find & Select drop do ...
Copy one range and paste in another range
Below is a macro, just copy and paste it into a module in your workbook and go from there.This macro uses message b ...

Helpful Excel Macros

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
Vlookup Function That Searches The Entire Workbook - UDF
- This UDF is a Vlookup function that searches the entire workbook in Excel. The syntax and usage of the function is exac
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
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 set up the conditional formatting in a spread sheet to highlight / format an entire row depending on the information found in cell. EX: highlight the entire Row B once shade if cell B10 = Accepted and another shade if B10 = Rejected. I am only acquainted in the most basic sense with conditional formatting and formulas and even less so in Excel 07 and 10.

Hi guys,

I have been messing with this for about an hour. I have a sheet with 400 rows, I need conditional formatting to highlight the entire row when the value column AK and AF do not match.

Can this be done with conditional formatting and if not VBA?

Back again since i got good help yesterday...

what im looking to do now is color fill a cell based on the value of an entire row.

What i have is a sheet that shows our current orders. The first column has the order number, which covers two rows. From there, the first row for that order is "Quantity Ordered" and the second row is "Quantity Received". From there, it is about 200 columns long.

What i am looking to do, is have three variables to determine the color of the cell with the order number in it.

1. One color to show the order has not come in yet (basically the received line is empty and does not match the ordered line).
2. One color to show the order is partial (this would be SOME values in the received line match values on the ordered line).
3. One color to show received (this would be when all the values of the received line, match all the values on the ordered line).

thanks again!

P.S. using excel 2007

I was wondering if anyone can help me with this problem

I would like to highlight an entire Row if the number is >17

I have tried to use conditional formatting; and i'm hoping its something rather simple that i'm over looking

I'm trying to get the anything in column J >17 to high light the entire row

Is there a simple way of doing this or does conditional formatting allow this or would it simply highlight that information that is >17?

If someone can point me in the right direction that would be gretly appriciated

I asked this questions yesterday but left out the i need to do this for the entire column A if Column L < 2. When I used =L2< 2 for the condition it only gives me what I need in A2. I need this condition for each cell in A to match the same cell in L. EX. A2 Highlighted when L2


I have run into a problem with conditional formatting which I am fairly new to. I am trying to apply a conditional format (color code) to a cell to match the conditional formatting of another cell which is linked to a lookup table.

So, for example my first conditional format (in Excel 2007) is Cell Value = "Rates", Format = Orange, Applies to entire worksheet (or =$1:$65536). So anything on the worksheet that has the word "Rates" in it is colored orange.

Second, I have a lookup table where a name is associated with the word "Rates". So C2 Product Category says "Rates" and D2 has a name (Joe Smith) and so forth. I have figured out how to have Joe Smith (B2) highlighted orange as well based on conditional format of "=$C3="Rates"" applied to the entire lookup table. I have other 'Product Categories' besides Rates and different names, so the names need to be matching the color associated with the product category. However, I would like to do the same thing for another worksheet that has the name "Joe Smith". So if Joe Smith appears somewhere else, it would appear to be orange solely because "Rates" is orange.

Hopefully this makes sense. How can I accomplish this?



A B C D Worksheet 1 Seat Num ID Product Name R31 03AZ005 Rates Steve Bentley R29 03AZ008 Rates Mike Bosi S25 03BF014 X Rates Sales OPEN S34 03BJ005 RMBS Michael Zigrossi S32 03BJ008 RMBS Alvin Sarabanchong Worksheet 2 Rates Q: How do I make these names match the corresponding Product color? RMBS --------> Mike Bosi Michael Zigrossi

Hi any help you could please give me would be greatly appreciated.

So I am trying to use conditional formatting to highlight an entire row if the text in two seperate columns are certain words.

What I've tried is Conditional Formatting>Use A formula to determine which cells to format
Then the formula I have tried (amongst others) is:

To break it down I need the whole of row 4 to be highlighted only IF cell G4= Chain AND Cell I4= YES

This appears to do nothing even when the values in row 4 match up.

Please could someone advise me where I am going wrong?

Many thanks,



I have a range of data that I would like to conditionally format all cells that have an error to have white font. Unfortunately, I cannot use a function like this:


because the formulas in this range are already too long to be able to duplicate for the 'value if false' argument. I have also tried using the conditional formatting tool by setting "Formula Is" equal to:
but this only whites out errors in cell A1 or column A (depending on what I selected.

I have pasted the formula below that is giving me the error if anyone has any ideas on how to modify the formula as well. It is basically an index match formula that is looking at several different data points on another worksheet and averaging them. Thanks in advance.

=AVERAGE(INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$2,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH( $C18,SRD!$A$4:$A$351,0),MATCH(M$3,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$4,SRD! $D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$5,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351, MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$6,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$ 7,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$13,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$ DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$8,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),M ATCH(M$9,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$10,SRD!$D$2:$DG$2,0)),INDEX(SRD !$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$351,0),MATCH(M$11,SRD!$D$2:$DG$2,0)),INDEX(SRD!$D$4:$DG$351,MATCH($C18,SRD!$A$4:$A$ 351,0),MATCH(M$12,SRD!$D$2:$DG$2,0)))

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

I have a long spreadsheet with info through column AT and Rows through 22. In Column S I want to input Conditional Formatting that will highlight the entire row of cells with yellow fill if my drop down menu in Column S is "Yes". So far I have only been able to formulate the Conditional Formatting to highlight the particular cell in Column S when "Yes" is displayed, not the entire Row. Does anyone know how to Conditionally Format an entire Row to be highlighted if a cell in that row displays certain info? Any responses are greatly appreciated! Thanks.

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 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 !

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?

Looks like the upgraded conditional formatting in Excel 2007 behaves very differently from 2003.

I'm writing a macro that uses conditional formatting to change the entire row color if the date in column C is before today. I have to support both Excel 2003 & 2007.

In Excel 2003, select the entire list range and assign this formatting formula. 34 is the last row of data


Looks like the upgraded conditional formatting in Excel 2007 behaves very differently from 2003.

I'm writing a macro that uses conditional formatting to change the entire row color if the date in column C is before today. I have to support both Excel 2003 & 2007.

In Excel 2003, select the entire list range and assign this formatting formula. 34 is the last row of data



I would like if possible that if 2 numbers were to match within a column then the entire row in which they are would highlight in a colour. Can this be done??



I have been working in and created a spreadsheet with tons of macro code to help create command scripts that I use to configure routers.
During my data research, I will locate a block of text within a large text file and copy that to a cell within my sheet. The general layout is each row contains all data related to a specific customer. Each column will contain specific types of data for all customers. What I need to be able to do is take one cell of multi-line text and compare it to the original text file (which might contain updates that I need to catch). So, I need to search within a large text file to match several lines of text from one cell, where one or more lines of text in that cell may not match the entire relative area in the larger text file. It may be just enough to find/match the entire cell with the complete text data. If I can't match the entire cell, I'll replace it completely with what I will have to manually find anyway. I hope I asked this properly.

I am using Excel 2007 and would like to be able to format an entire column based on the date at the top. I have a spreadsheet with each weekday at the top. Each date cell is actually 3 cells merged together so there are 3 columns below it. I would like all 3 of those columns to be highlighted on the column for the current day. I used conditional formatting to format the cell at the top that contains the current day, but I haven't been able to figure out how to highlight the entire colunn. Thanks for any help or direction you can provide.

I've run into a problem with VBA in Excel 2007 while working with conditional formatting. I need to apply a conditional formatting to a data range. However, the subroutine may be run again on the same set of data and I don't want to reapply the conditional formatting to the same data range if it already has the formatting applied. One approach would be simply to delete all conditional formatting on the entire data range, but there could be additional conditional formats which should remain in place. So I'm trying to find a way to cycle through all conditional formatting conditions to see if any of them match the one I'm wanting to apply. If any match what I'm wanting to apply, I'll just skip adding it again.

But I can't seem to find a way in vba to get the formula back out of excel.


Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _

I can have the fill color returned for a given format condition. But is there any way to return the formula in the format condition? I've tried:


Workbooks("testbook.xlsm").Sheets("sheet1").ListObjects("DataTable") _

but this seems to be only able to set a value, not return one.

Any ideas?



Hey guys,

I'm trying to create a calendar of sorts in excel for scheduling 'deals' to send out. I'm having trouble creating a conditional formatting function that can look horizontally to see if another cell contains the same content, if it does, highlight that original cell. Now the problem is the "time frame"/(cells) to check needs to be dynamic so I can use this one condition across the entire row but only looking back 60 days. I'm using a match function to check if it's true.

Attached is an example worksheet I'm working with, with conditional formatting already in place in cell BV22. Any help would be much appreciated.

KCCO - It's Friday at least.

I posted a thread this morning that the formula I started with is not working anyway so I need to start over. I need a forumla to conditionally format an entire worksheet. If column G is a different value than the row above it, I need to format the entire row across with a thick line. This cannot be done with regular CF.


Hi, I have just got a new laptop with Excel 2007 on it, and I am really struggling to adapt to it. I am trying to do some conditional formatting but having difficulty:

I have a sheet with all of my sales for the previous 12 months in columns B-M. Each row represents a different customer. So for example, in row 1 - A1=customer name B1:M1=units bought per month for the last 12 months.

There is a conditional formatting wizard in excel 2007 which allows you to insert a small dot / arrow in the cell. If the value is =67% is green.

If I highlight cells B1:M1 and hit Alt, H, L, I , then Excel applies this conditional formatting to the row. I now want to apply the same conditional formatting to all of the rows (1000+), without having to highlight every row individually. If I highlight the entire range (B1:M1000) before applying the formatting, Excel takes an average of the entire range (and I want the icons to represent how the value compares to the average of that particular row).

So after that rather long winded explanation, my question is: Is there an easy way to apply the same conditional formatting to a whole range of cells - by dragging somehow?

Many thanks in advance for your help,


Hello Everyone!

I'm using MS Excel 2013 and I need to know how to use Conditional Formatting Color for Entire Row.

Lets Suppose...
Cell E7 is No Blank functioned then an specific Color should be apply on Entire Row7.

I able to use Conditional Format for singe cell with (No Blank) function but unable to use it for Entire Row.

So Help me to solve this issue.

What is the proper syntax to highlight an entire row with conditional formatting if a cell contains the word total?

I have not worked with conditional formatting much at all. I have been able to apply conditional formatting to a column of data, but I would like to highlight the entire row instead of just the one cell in that column.

Say I have five columns of data for each record (ID Number, Address, City, State, ZIP Code). I know how to apply conditional formatting based on the zip code and have Excel highlight the cells in the zip code column that meet my conditions. What I would like to do is have it highlight not just the zip code, but also the ID number, address, city, and state when a zip code meets a condition.

Thanks, Spence