if statement that colours a row

November 1, 2010 at 19:33:32
Specs: Windows Vista
I am trying to make a if statement that colours a row depending on how close to the expiry date it is. Like 30 days till renewal is red, 60 days is another colour and so one think uptill 120 days or greater just leaves the row blank.

I do have a field that pulls the current date from the system that it is run on, so that there are 2 dates to do the calculations with.

I have been able to get it so it calculates the number of days between the 2 dates as well


See More: if statement that colours a row

Report •


#1
November 1, 2010 at 20:58:32
Thanks for letting us know what you are trying to do.

If you need some help, feel free to ask.


Report •

#2
November 1, 2010 at 21:03:06
k this is me asking for help with my forementioned issue

Report •

#3
November 1, 2010 at 21:47:50
Use Conditional Formatting.

You didn't say what version of Excel you are using, so the steps to apply Conditional Formatting are different, but the formula (or "rule") is the same. Look up Conditional Formatting in the Excel Help files for the steps required to apply it.

Let's say your expiry date is in A1.

Select the row you want to format and use:

First condition (or rule)

=$A$1 > TODAY() - 31 and choose Red fill as your format.

Second condition (or rule)

=$A$1 > TODAY() - 61 and choose another color as your format.

Third condition (or rule)

I'll leave this up to you to figure out.


Report •

Related Solutions

#4
November 2, 2010 at 14:28:40
I am using excel 2007, I need the Conditional Formatting to work for all the rows, to colour the entire row, as well to be a automatic occurence, so i dont have to apply it manually every time i open the item

This is just so i can see who needs to be reminded to go and renew there stuff. The only data that after the first input, will be if i need to and a new person or remove on.

I do thank you for the help you have been giving as i work through this issue


Report •

#5
November 2, 2010 at 15:29:23
First, I doubt you really need "the Conditional Formatting to work for all the rows, to colour the entire row".

Do you really have 1,048,576 rows of data?

Do you really have 16,384 columns of data?

Regardless, the method I described in my previous response should work for you.

In my example, I am using Column A to hold the expiration dates.

1 - Select all of the rows to which you want to apply the Conditional Formatting.
2 - Click Conditional Formatting
3 - Click New rule
4 - Click "Use a formula to determine which cells to format
5 - In the box under "Format values where this formula is true" enter this formula:

=$A1 > TODAY() - 31

Since the $ locks in the Column (an Absolute reference), the formatting for the entire row will only be based on the date in Column A. Since we didn't use a $ for the Row (a relative reference) the Conditional Formatting formula will apply to each row individually, so that each Row will be based on what is in Column A of that Row.

6 - Click the Format button, then the Fill tab and choose a color.
7 - Enter a date in Column A in any of the rows you selected in Step 1.

If that date is within 30 days of the system date, the row will be formatted in the color you chose. Since the TODAY() function will always return the system date, there is nothing for you to update.

8 - For the 60 day period, use CF, Manage Rules, New Rule and enter:

=$A1 > TODAY() - 61

9 - For the 120 day period, use CF, Manage Rules, New Rule and enter:

=$A1 > TODAY() - 121

Use the up and down arrows put the rules in order from the shortest time period to the longest.


Report •

#6
November 2, 2010 at 18:23:17
DebryDad03 i know i may not have the entire sheet filed, but what i am needing is for it to do all the fields that do have data filed in and to be automatic when the file is opened. Where what your stating only does the field that is selected and has to be applied manually every time it is open.

The below is an example of what i have:

System Date: 11/2/2010 [this is gotten by: =NOW() ]
Expiry Date: Days Till Expired
25-Sep-11 323 [this is calculated by: =DAYS360($D$1,$D3)]
6-Jun-11 214
1-Aug-11 269
25-Dec-10 53
30-Oct-10 -2

That is why i felt that a If statement would be required and am having difficulty getting the CF working as u meantion.


Report •

#7
November 3, 2010 at 05:47:02
First, a formula (e.g. an IF statement) that is placed in a cell cannot format a cell. A formula in a cell can only return a result in that cell.

However, a formula used in Conditional Formatting can be used to format a cell or range of cells as long as it evaluates to TRUE. Whenever it evaluates to TRUE, the Conditional Format feature applies the format to the range.

re: what i am needing is for it to do all the fields that do have data filed in and to be automatic when the file is opened.

Where what your stating only does the field that is selected...

True, which is why I said to select all of the rows that contain your data. You can even select the entire sheet and apply the CF to every row if you choose.

and has to be applied manually every time it is open.

Not true. Conditionally Formatting remains in effect when the workbook is saved.

re: am having difficulty getting the CF working as u meantion.

Since you are having trouble with the concept of the example I offered, let's try it with the specific data you used in your last response.

To test this, please set up a worksheet just like mine so that there is no confusion.

My worksheet looks like this:

 	       D	 E
1	11/03/10 7:56	
2	Expiry Date	Days
3	09/25/11	322
4	06/06/11	213
5	08/01/11	268
6	12/25/10	52
7	10/30/10	-3
8		
9
10		

1 - Select Rows 3:10. I am purposely selecting blank rows to show you that you can apply CF to a larger range for future use.

Now, we are going to do the CF in 2 steps so you can see how the evaluation of the formula used impacts the CF.

2 - Click Conditional Formatting...New Rule
3 - Choose "Use a formula..."
4 - Enter =$E3 < 31

Note that in the Applies To: field it says =$3:$10. That means that formula (=$E3 < 31) is applied Row by Row and evaluates to TRUE whenever the value in Column E is less than 31. In other words, even though you don't see it, in Row 4, the formula is actually =$E4 < 31. In Row 5 it's =$E5 < 31. For each Row where it evaluates to TRUE, it formats the Row.

5 - Choose a format (e.g. Red Fill)
6 - OK your way out.

The Red fill format should now be applied to Rows 7:10 since they are all less than 31.

Now, we don't want the Rows with nothing in Column E to be formatted, so let's fix that:

7 - Choose CF...Manage Rules
8 - Double click the formula and change it to:

=AND($E3 <> "", $E3 < 31)

This will only evaluate to TRUE when there is a value in Column E and it is less than 30.

With that formula, only Row 7 should be Red.

9 - With Rows 3:10 selected, Click CF...Manage Rules...New Rule
10 - Enter: =AND($E3 <> "", $E3 < 61)
11 - Format it as Yellow fill
12 - Since Rules are applied in order, use the Down Arrow to move the New Rule (< 61) down below.
13 - OK your way out.

Row 6 should be Yellow, Row 7 should be Red.

14 - Continue adding rules and positioning them in the right order until you have all of your date ranges covered.


Report •

#8
November 3, 2010 at 09:27:46
DerbyDad03

Thanks for putting it that way it that way and worked better for me. I dont know what i did wrong to have the CF not work when i opened the workbook up again. It is also doing the entire row instead of just the one cell in the row.

Thanks again for your help and patience with me


Report •


Ask Question