# New to Excel



## BillieKopecky2022 (Dec 30, 2022)

Hello, 
I am new to Excel. I am trying to adjust a spreadsheet so that an entire row will be highlighted when a date in a column (k) is past. The dates in Column K are all different. How do I do this?


----------



## Joe4 (Dec 30, 2022)

Welcome to the Board!  

Let's say that you wanted to apply this to rows 1-100.
Then select rows 1-100, go to Conditional Formatting, select New Rule, and choose the "Use a formula to determine which cells to format" option and enter this formula:

```
=$K1>0
```
and choose your desired highlight color.


----------



## BillieKopecky2022 (Jan 2, 2023)

Joe4 said:


> Welcome to the Board!
> 
> Let's say that you wanted to apply this to rows 1-100.
> Then select rows 1-100, go to Conditional Formatting, select New Rule, and choose the "Use a formula to determine which cells to format" option and enter this formula:
> ...






I am not understanding the rule you sent. 
The above picture is a snip it of my spreadsheet. Based on the date is column K I need the row to change Green when the date is past. The date is not always the same. I would like this function of highlighting the row to happen automatically when I open the spread sheet every day.


----------



## mumps (Jan 2, 2023)

To follow up on @Joe4's suggestion:
Select all the data
In the "Home" tab, click on "Conditional Formatting"
Click "New Rule"
Click "Use a formula to determine which cells to format" 
Enter this formula:  =AND($K1<NOW(),$K1<>"")
Click "Format" and select the fill colour
Click "OK" to exit all pop ups.


----------



## BillieKopecky2022 (Jan 2, 2023)

I typed in the formula you suggested, and it didn't work.


----------



## mumps (Jan 2, 2023)

It worked for me.  Make sure you follow the steps exactly as listed.


----------



## BillieKopecky2022 (Jan 2, 2023)

Okay sorry I got it to work.
 The problem is I need the row to change as the date in column K passes. When I used the formula, it highlighted all the rows immediately.


----------



## mumps (Jan 2, 2023)

BillieKopecky2022 said:


> I need the row to change Green when the date is past


The formula compares the dates in column K to today's date.  If any date in column K is before today's date, that row will be coloured in green.  In Post #3 you posted a picture.  It is hard to work with a picture.  It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (*not a picture*) of your sheet.  Alternately, you could upload a copy of your file (de-sensitized if necessary) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.


----------



## Sufiyan97 (Jan 2, 2023)

Try TODAY() instead of NOW()

Book7ABCDEFGHIJKL121/2/202331/1/2023412/31/202251/3/202367891011Sheet1Cells with Conditional FormattingCellConditionCell FormatStop If TrueK1:K100Expression=AND($K1<TODAY(),$K1<>"")textNO


----------



## BillieKopecky2022 (Jan 4, 2023)

Excel help.PNG
					

Shared with Dropbox




					www.dropbox.com
				




Here is the link.
I believe I have the formula in. I have multiple formulas for cell K and only one is working. How do I make sure all the formulas are working?


----------



## BillieKopecky2022 (Dec 30, 2022)

Hello, 
I am new to Excel. I am trying to adjust a spreadsheet so that an entire row will be highlighted when a date in a column (k) is past. The dates in Column K are all different. How do I do this?


----------



## Fluff (Jan 4, 2023)

That link is to an image. What we really need is the actual file, or for you to post your data (including the conditional format rules) using the XL2BB add-in.


----------



## BillieKopecky2022 (Tuesday at 1:22 PM)

I tried to add the XL2BB add-in, and it's not supported on the file. How do I upload the actual file?


----------



## Fluff (Tuesday at 1:26 PM)

You will need to upload to a share site such as OneDrive, DropBox, GoogleDrive. Then mark for sharing & post the link you are given to the thread.


----------



## BillieKopecky2022 (Tuesday at 1:29 PM)

Okay, I think I got it this time.  








						Excel Help.xlsx
					

Shared with Dropbox




					www.dropbox.com


----------



## Fluff (Tuesday at 1:53 PM)

Thanks for that.
The CF is working for me, in what way is it wrong?
Excel Help.xlsxABCDEFGHIJKL1ENTRef MDMng MDLast PSGAHIImplant DateSuggested Tune Actual TuneRemoteSuggested Check-in Check InOV2MasdonTenchavezMasdon10/06/2158.8010/14/2211/13/2211/16/22Legacy12/16/2212/22/22, 1/25/233Withrow9/21/2210/21/2212/5/22BT1/4/234MasdonTenchavezCorey06/19/1646.5011/11/2212/11/2212/14/22Legacy1/13/231/3/235MasdonGannuchCorey11/08/1917.0012/2/221/1/231/23/23BT2/22/236MasdonMasdonCorey05/19/2117.0012/9/221/8/231/11/22BT2/10/227MasdonCarpenterCorey02/16/2228.112/5/231/4/241/11/23BT2/10/238MasdonNixonCorey08/07/22101.201/6/232/5/2302/22/23BT9MasdonSerioCorey03/30/2219.301/20/232/19/2302/22/23BT10MasdonMasdonCorey10/27/2270.101/27/232/26/2303/01/23BT11MasdonCarpenterCorey01/11/1668.902/10/233/12/2303/15/23BT12MasdonMasdonCorey12/22/2238.502/17/233/19/2303/22/23BTSheet1Cell FormulasRangeFormulaJ2:J7J2=H2+30G2:G12G2=F2+30Cells with Conditional FormattingCellConditionCell FormatStop If TrueH2Expression=AND($K1>TODAY(),H1<>"")textNOL2Expression=AND(L2>TODAY(),L2<=TODAY()+7)textNOL2Expression=AND(L2>TODAY(),(L2<=TODAY()+14))textNOJ2Expression=AND(J2>TODAY(),(J2<=TODAY()+14))textNOH2Expression=AND(H2>TODAY(),(H2<=TODAY()+14))textNOJ2:J7Expression=AND(J2>TODAY(),J2<=TODAY()+7)textNOH2:H7Expression=AND(H2>TODAY(),(H2<=TODAY()+7))textNO


----------



## BillieKopecky2022 (Tuesday at 3:16 PM)

Cell K has three functions and only one of the functions is currently working. There is a function to change the color of the cell to yellow at 14 days from the date in the cell, a function to change the cell orange when the date is the cell is 7 days before the due date and then a function to change the whole row green when the due date in the cell is past. Only the function turning the cell orange is working.


----------



## BillieKopecky2022 (Tuesday at 3:17 PM)

BillieKopecky2022 said:


> Cell K has three functions and only one of the functions is currently working. There is a function to change the color of the cell to yellow at 14 days from the date in the cell, a function to change the cell orange when the date is the cell is 7 days before the due date and then a function to change the whole row green when the due date in the cell is past. Only the function turning the cell orange is working.


Sorry cell H not cell K.


----------



## Fluff (Tuesday at 3:35 PM)

That's because only the Orange formula is looking at rows 2 to 7, the others are looking at just row 2


----------



## BillieKopecky2022 (Tuesday at 3:46 PM)

Oh...goodness okay. Thank you.
I am still having a problem with the function to change the row green when the due date (date in the cell H) is past. I need cell A-L to turn green not just the cell and only the row of the due date cell.


----------



## Fluff (Tuesday at 3:55 PM)

Change it from K1 & H1 to K2 & H2. The row should match the first row in the applies to range.


----------



## BillieKopecky2022 (Dec 30, 2022)

Hello, 
I am new to Excel. I am trying to adjust a spreadsheet so that an entire row will be highlighted when a date in a column (k) is past. The dates in Column K are all different. How do I do this?


----------



## tj4242 (Tuesday at 4:02 PM)

Ignore any message here from me.  My browser was way out of date.


----------



## BillieKopecky2022 (Tuesday at 5:03 PM)

Fluff said:


> Change it from K1 & H1 to K2 & H2. The row should match the first row in the applies to range.


That didn't work for me. I don't think I have the right formula. I need the whole row A-L to be highlighted when the date in Column K is past.


----------



## Fluff (Wednesday at 6:19 AM)

In that case you need to select A2:L100 and use

```
=AND($H2<TODAY(),$A2<>"")
```


----------

