Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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

VBA IF Statement in Excel Macros
IF statements allow you to make decisions in your Macros and VBA in Excel. An IF statement gives you the power to ...
Break out of or Exit Different Types of Loops in VBA Macros in Excel
How to Exit, End, or Break out of loops in Excel. This tutorial includes breaking out of While, Do, and For loops. ...
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 r ...
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 ...

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


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

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?

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?



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,


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

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 large spreadsheet which consists of about 10 columns. It is basically a listing of information found in a human resources database, so information varies. However, in the last column of each row there is either a "0" or a "1." How do I create a conditional format to highlight the entire row if the value in the last cell of that row is "1." and apply it to every row. I just want rows highlighted if I find the number"1" in last column.

Hi Guys,

I have a spreadsheet with important info in columns H & N. I need to highlight rows in which two values in column H match but the corresponding values in column N do not match.

This equation works for highlighting rows in which two values in column H match and the corresponding values in column match as well:


Is it possible to modify this in order that it will highlight the rows when the cells in column N do not match after the initial condition.

All the best,


I have 2 lists of data, one in column A and the other in column B and neither list is any specific order. I'm looking for a way to search Column B for a match to each piece of data in Column A and then use conditional formatting to highlight the cells in Column A that did not find a match.

1 tree flower
2 grass tree
3 water dirt
4 flower rock

I'd like for cells A2 and A3 to use conditional formatting to say have a red background because they do no have a match in Column B. I'm OK with how to do conditional formatting, just having trouble getting the formula working within the conditional formatting rule setup.

Thanks for your help.

I am about to pull my hair out. Does any one know how to speed up Conditional Format Calculations or MATCH/Vlookup. I am using Excel 2007. I have a spreadsheet that I have to search in everyday. It has about 96,000 line items. I have a list of values that I have to match up that changes daily. If I do highlight duplicates and sort by format color than it take almost an hour to sort and of course it lock up all my other excel sheets I have open. Can some one help me please.



I'm doing some conditional formatting, where if the first 3 cells in a sheet are empty (or zero), then the whole line is 'grayed' out.

So for Cells A2 to A10 the conditional format is...

and conditional formatting (gray shading) is applied to cells: =$A$2:$A$10

The problem is I have approx 200 row to apply this too.. So I'm having to do the same formula in Conditional formatting 1 line at a time.

I tried doing an entire range, but if A1 to A3 had a value, then the conditional formatting won't apply for the whole range, where it should only not apply for row A

Is there an easier way then having to do this line by line for hundreds of lines? I'm sure there is, but I cannot figure it out..

Anyone know please?

I have a requirement to copy entire column from sheet1 to sheet2 to based on the condition that the headings for each of those columns match using vb code.

The sheet 1 contains some 32 columns and I need only 18 columns to be coppied to sheet 2.
The columns that have to be copied are not in specific order. But the sheet2 contains all the column name in cells A1,B1,C1.... so basically I want to see if the column in A1 of sheet2 is present in Sheet1 then copy the entire column A into column A of sheet2.
I am new to vb coding so the requiremnt may not be clear. Please guide as to what can be done to compare the column headings in 2 different worksheets.

How to Highlight the Entire Column Based on Specific date, Thursday & Friday to be exact, using conditional Formatting.

I have used Conditional Formatting to highlight several cells in a worksheet.

I want to be able to count the number of the highlighted cells in each row.
If the number of the Highlighted cells is greater than a number, say 2, then I want to use Conditional Formatting to highlight the entire row.

I think I know how to highligh the entire row, but have not figured out how to count the number of highighted cells in the row. To be counted do the cells have to be the exact same color - mine are not?

FYI - know nothing about use VBA - hope this can be done using straight Excel 2007

I have a lot of data and it's hard to find stuff among different categories.
I frequently sort and filter rows of data, so I need a dynamic solution.

I"m envisioning adding two buttons. One to enact the formatting below, and
another to remove the formatting below. Thanks for your help!

Starting at Row 13 and through Row 3000

Col A Col B Cols C through AS
Apples Little Entire row should be light green
Apples Little Entire row should be light green
Apples Big Entire row is blue, but insert line between big
and little row
Apples Big Entire row should be light green
Apples Big Entire row should be light green
Oranges Bad Entire row is now white, insert line for row between
apls orngs
Oranges Bad White Row
Oranges Good White Row, insert line
Pears Fine Entire row is now light green, insert line

Thanks... I may be getting ahead of myself because I have another thread on
a seperate subject to close, but I intend to work on these over the weekend...

My workbook is attached.

Under the 'Current Contractors (Melvindale)' sheet, I am looking at column F. I have conditional formatting to highlight the cell in yellow if it is 4 days before the due date, and Red if it is the day of the due date or past the due date. It is currently only selecting the cell in the F column, but I want it to select the entire row that that cell belongs to. I have never used conditional formatting, and the current formula that I am using, I retrieved from this website:

Please Help

What would be faster - doing an exact match VLOOKUP in an entire column, e.g. VLOOKUP(A1, B:C, 2, 0) or creating a dynamic named range in columns B and C with a formula that searches the entire column to find the last used row, e.g.
=OFFSET($B$1, 0, 0, MATCH("*",$B:$B,-1), 2)

and then having each VLOOKUP search in this named range? Is the MATCH in the named range formula calculated each time for each VLOOKUP?

What if I were to have another cell, say D1 and set it equal to

then define the named range as
=OFFSET($B$1, 0, 0, $D$1, 2)

Would that be faster at all? If I am in a sheet set to manual calculation, would that even work since D1 needs to be calculated before the range is set?