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

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

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

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 !

Help please!

I've created a spreadsheet with a cell containing today's date.

I then have another cell where I want a 'use-by-date' of a product (mm/yyyy)

1. I want to conditionally format the 'use-by-date' cell to highlight in yellow when its within so many days from today's date?

2. If the product is used (the mm/yyyy deleted) the cell will highlight in red until a replacement product's 'use by date' is entered.

I appreciate excel may cause me a few problems recognising the end-of-month date and hence I understand the zero of the month trick but I'm really head scratching on how to incorporate this into an entire formula for the cell.

Can anyone offer help or advice?

Thank you in advance

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.



im using the conditional formatting to highlight an entire row ->

im using

=$F3=today() to highlight yellow it if F3 hits todays date

ive set another conditional format up with

=$H3=today() to highlight red when it hits this date

i know im nearly there but i want the yellow to highlight until we hit red the period is 11 days and then stay red permanently

Is this possible

Again help much appreciated

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?


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)

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,


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)

attached is an example of the "leave tracker" i am working on. once you open you will see that the following is already conditionally formatted. if any cell is empty it will highlight orange. in the "from" column, if the date is current or past it will turn green. in the "to" column if the date has reached or past it will go red. if the from or to date has not been met then it will not be colored. what i would like to do is the following. if the from date has been reached then highlight the entire row green. if the to date has been reached or past then turn the entire row red.

also as a bonus question i would like to move all expired rows to a second sheet, so i can show a history. if i can do this then i would like that to happen the day after the "to" date has been met, moved onto the leave history tab, and delete the row from the leave tracker tab. id prefer to do this with conditional formatting or formulas, and not macros. its sometimes difficult to run macros in military computers.

if your in the army, or military in general, i will pass this along to you after i have it completed. i also have a pretty gnarly alpha roster that gives you some pretty awesome breakdowns, and makes battle roster numbers for you. my profile tracker also does some pretty good breakdowns.

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 am trying to use conditional formatting to call attention to a row that contains the current date. I can easily highlight a cell containing the date, but is there an easy way to highlight the entire row, and not just the cell?

Would it be easier to use a macro? I also know that I can sort my table and manually highlight the rows, but I don't want to have to sort the table each time.

Thank you for your help!

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 want to highlight entire row using conditional formating from the RANGE

I Know how to highlight the entire row using only one row but i want to

highlight the row from the available RANGE !!

Example when i Entire Sunday it highlights the entire row

but if i enter monday it dosent work . How to make it work so that if i enter tuesday it highlight the tuesday row .


Sample excel attached


I'm new to conditional formatting and I want to conditionally format an entire row based on a single value, i.e. the values based in column B below. However, I want each rating to have its own colour, for instance, a 10 Rating would highlight the entire row Green, a rating of 2 would highlight the entire row red, a rating of 5 would highlight the row yellow etc..

Product (Column A)

Rating (Column B)

Description (Column C)
Very Good

Any help on this will be much appreciated. Thanks

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


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 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 6 steps in a process, each step has a status cell.
After each step is completed, the worker puts the completion date in the appropriate cell.

I'd like to know when it been over 14 or 21 days since the latest action.

I'd like to have excel check the 6 steps and if the latest date of the 6 steps is over 14 days old... highlight the cell yellow and if the latest date of the 6 is over 21 day old... highlight the cell red.
An example is attached (done manually).

Thanks in advance for any help.


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.

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?