Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
Subscribe for Free Excel tips & more!

Using Conditional Formatting To Highlight Entire Row Based On Date In One Cell

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

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.

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 ...
List all Conditional Formatting Formulas in Excel
List all conditional formatting formulas in a worksheet in Excel. This allows you to quickly view and manage all of ...
Highlight and Sort the Top and Bottom Performers in a List in Excel
How to highlight the rows of the top and bottom performers in a list of data. This allows you to quickly identify ...
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 ...

Helpful Excel Macros

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
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 the Row of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the row of the active cell with a solid
Automatically Highlight the Active or Selected Cell
- This macro will highlight the active cell in any excel spreadsheet with a color selected in the code. This means that a
Highlight the Row and Column of the Selected Cell
- This macro will highlight the row and column of the active cell. This will fill the column and row of the active cell w

Similar Topics

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

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

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



If any date in column E is more than 365 days older than todays date I want the entire column to highlight (not just that cell).
Not every cell in column E has dates either. Some cells are blank and some cells have other text.
So how can I get the entire column E to highlight if a date is found that is over 365 days from todays date?

Also, if you know how to do this a breakdown describing what each function you're typing is would be great. Rather than just get the code I would like to understand it (and learn) as well.

I have a spreadsheet with a bunch of columns (with corresponding rows) with one column being dates. I need a vba macro to check if the dates in the column are after today's date. If so, then I want to highlight the entire row in a certain color, say yellow. The date column is the last column on the right so the highlighting would be done from column A to the date column (column G). I can use conditional formatting but it only highlights that one (date) cell that meets the condition, not the entire row.

I just stumbled across this site recently and found it very informative! I just couldn't find a post that relates to my problem.


I have a spreadsheet with dates starting in Cell H57. I would like to conditionally format from that cell down onthe following conditions:

If date is between 3 and 7 days, highlight green
If date is between 8 and 15 days, highlight yellow
If date is equal or greater than 16 days, highlight orange.

Can I do this in the formula conditional formatting?


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,


Okay, I hate to even post because there are so many conditional formatting questions and answers out there, but everyone has a different way they want to do things, and I can't find a way to make mine work. I'm able to get some things working, but at the same time, it will also be highlighting cells that don't meet the criteria.

Here's what I need.

I have a list columns A-M containing data, C and M are date fields. C will always have a date, and M will only have a date when the row has been "Approved."

If the date in C is greater than 12 days away, I would like the entire row to turn yellow.

If the date in C is less than 12 days away AND the corresponding cell in M is blank, I need the entire row to turn red.

I have managed to get the C cell that is greater than 12 days away to turn yellow by using the "Format only cells that contain" rule with Cell Value - Greater Than - =TODAY()+12

However, it only highlights the cell. I tried using the "Use a formula to determine which cells to format" but couldn't get the syntax right.

I've also messed with the ISBLANK to try to get the row to turn red, and had some close calls, but for some reason it would highlight a few rows that actually had data in them.

Thanks for the help!

Greg (broscup)

I am trying to create a formula that will highlight an entire row based upon a date.

Example: I have vacant units in my spreadsheet and once the upgrade takes place and someone moves into that vacant unit I want the row to automatically highlight based upon the move in date.

Is that possible? Attached is an example of what I am trying to do.

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.

I have a sheet (A2:F86) that has a column of dates that change color. (i.e. E2 changes to green within 90 days, Blue within 60, Yellow within 30, Red if past due.) I have used conditional formatting to get the column to work right, but I would like it to highlight the entire row that is changing, not just the cell itself. Here are some of my formulas:

H1 =Today()
E2 =EDATE(D2,12)
E2 =(E2-$h$1)

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?

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

We have a spreadsheet which we use to track work progress. In it we have a column which lists a date due for projects. By design, a few of the cells are blank. What I would like to do is use conditional formatting to highlight all cells in blue which contain dates within 90 days of today's date to alert our members of the upcoming due date. I'd like to highlight all cells in yellow that are past due by 1 to 60 days. And, I'd like to highlight all cells in red where the due date is delinquent by greater than 60 days. What conditional formatting codes do I use to accomplish these requirements? Thanks in advance.

I got a nice bit of code back in the old excel 2003 days about this but I lost it since i changed to 2007, here is the description:

On whatever row I select a cell, make that entire row highlighted with a color or somthing.

Just that simple. I want to click on a cell and make the entire row highlight automatically. If I click another cell, the highligh needs to follow. If I ress the down or up arrow, the highlight just follows theselected cell whereve it goes highlighting the entire row



I have a column of dates that needs to be compared current date and
highlighted if the date is less that today's date.

The dates were populated in a general non-specific format (below) and vary
in length. My plan was to create a macro, that uses conditional formatting
to say; if the cell date is less than today's date highlight it yellow. I
am not finding this to be an easy task! Anyhow, after creating the
conditional formatting; I apply it the column by using a past special, then
lastly apply a format to the entire column of dates as DATE "*3/14/2001".
What happens really doesn't work like expected. The date reverses itself in
the format of 05/31/2006 without applying any highlight for the conditional
format. Does anyone know how, I can get this to work? I don't mind the
date being reversed, but it's not being recognized or highlighted from the
conditional formatting. Help!

The column below should have two dates highlighted in yellow (second and





I am building a template to track assignments that are more than 14 days old. I have a formula in one column of cells that will give me the difference between the date we received the assignment, and today's date. Now what I need help with is conditional formatting. I want to be able to somehow either highlight with red the entire row of any assignment that is over 14 days old, or just one cell so I can have instant notification of anything that is getting too old. Everything I have tried so far gives me a red highlight on all cells except those cells that have a number less than 14. In the cells where I have the date the assignment is recieved I will have blanks until I actually have an assignment, which seems to be effecting the conditional formatting, making the cells where the dates are not entered yet show up as red highlights as well, which I don't want. This seems like it should be a simple thing, but I can't find anything to help me format all of this correctly. Any help would be appriciated.

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

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?



Hi all,

I have a date in Cell A3. I also have a date in cell L3. What I need to
do is this: If the date in Cell A3 is more than 7 days old (from
today's date) and and cell L3 is blank or empty, I need to highlight
the entire row. Can I do this using conditional formatting?

Thanks so much!

Hi to all,

I know that you can highlight a cell or it's entire contents with a certain color, but can you highlight only certain portions of it's contents using Conditional Formatting?

A simple example: If cell A1 contains the text "The pink elephant has blue and green spots." and cell B1 contains the text "blue", using cell B1 as a reference, is there a way to highlight only the text "blue" in A1 with the color blue using Conditional Formatting?

Thanks in advance for any answer to this.


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

To all, (Excel 2010)
Apologies if this is already answered elsewhere but I have searched for my answer here and throughout Google Land. I am sure this is very easily accomplished by others so here is my questions: I do not want to have to use the VBA coding thing to reach my goal. I have multiple rows of "Features of Work" that span from A2 to F2-G2 where the start date is F2 and the Finish Date is G2. (Note: This is a construction schedule pulled from P6) I want the entire row to highlight from A2 to E2 when F2 (Start Date) is TODAY OR GREATER. I was able to do so with a single row (although it wont highlight the accounting or percentage columns?) but I need it for the whole sheet. Do I have to apply a conditional format to each row? That would take forever and may slow the file down?

Thanks to you all from FREEZING ALASKA!

I want to highlight an entire column based on whether a cell in that column is equal to today's date.

I know how to write a loop that cycles through all of the columns until it finds today's date and then modify the column's properties, I was wondering if there was an easier way to do it. Also, I want the highlighting to be temporary meaning it should not save.

Any ideas?