I have column A that I want to turn the back ground red if the number of
months is = 12 in column I. If the number of months is =< than 11 I don't
want a back ground. I can either get it to turn red for both instances or
stay the same for both instances. Please help!!!!

Let's assume you want to format the range A1:A5 if the corresponding cell in
I1:I5 =12. Assuming the values in column I are numeric integers.

Select the range A1:A5
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(I1),I1=12)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...
I have column A that I want to turn the back ground red if the number of
months is = 12 in column I. If the number of months is =< than 11 I don't
want a back ground. I can either get it to turn red for both instances or
stay the same for both instances. Please help!!!!

"T. Valko" wrote:

Let's assume you want to format the range A1:A5 if the corresponding cell in
I1:I5 =12. Assuming the values in column I are numeric integers.

Select the range A1:A5
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(I1),I1=12)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...
I have column A that I want to turn the back ground red if the number of
months is = 12 in column I. If the number of months is =< than 11 I don't
want a back ground. I can either get it to turn red for both instances or
stay the same for both instances. Please help!!!!

It works but I still have a problem. The A column is stay red because when I put my formula in column I the column shows 1311 all the way down and I don't know why. My formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m") and the conditional formatting is:
Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15
I want column I to be covered by black until typing a date in column A. I
want column A to have no background unless the date typed into it is more
than 12 months old. I do not want column A covered by black though. If I
change the 1311 in column I to zero then I lose my formula.

Ok, let's get this straightened out...

formula in column I the column shows
1311 all the way down
formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m")

That means the cells in column A are empty. An empty cell evaluates to 0 and
0 is the date serial number for 1/0/1900 (actually, the true value is Dec 31
1899 but Excel doesn't recognize dates before 1/1/1900. The 0th day of a
month refers to the *last* day of the previous month. There's a little more
to it than that but I don't want to go off into an entirely different
topic!).

So, change your formula to:

=IF(COUNT(A5),DATEDIF(A5,NOW(),"m"),"")

Copy down as needed.

OK, now your formatting...

You actually need to apply cf to both ranges, column A and column I.

If you want to keep column I "blacked out" until a date entry is made in
column A...

Select the range of cells of interest in column I. Let's assume the range is
I5:I20.
Goto the menu FormatCellsPatterns tabselect BlackOK

With the range still selected, now apply the cf...

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=COUNT(A5)
Click the Format button
Select the Patterns tab
Select No Color
OK out

Now, apply the cf to column A:

Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15

Ok, what should happen if the cell value is greater than 15? I'm going to
take a guess and say you want the cell shaded GREEN.

Select the range in question. Let's assume the range is A5:A20

Conditional Formatting
Condition 1
Formula Is: =I5=12
Patterns tabselect a shade of RED
OK

Click the Add button

Condition 2
Formula Is: =AND(I5=13,I5<=15)
Patterns tabselect a shade of YELLOW
OK

Click the Add button

Condition 3
Formula Is: =AND(COUNT(I5),I515)
Patterns tabselect a shade of GREEN
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...


"T. Valko" wrote:

Let's assume you want to format the range A1:A5 if the corresponding cell
in
I1:I5 =12. Assuming the values in column I are numeric integers.

Select the range A1:A5
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(I1),I1=12)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...
I have column A that I want to turn the back ground red if the number of
months is = 12 in column I. If the number of months is =< than 11 I
don't
want a back ground. I can either get it to turn red for both instances
or
stay the same for both instances. Please help!!!!

It works but I still have a problem. The A column is stay red because when
I put my formula in column I the column shows 1311 all the way down and I
don't know why. My formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m") and the conditional formatting is:
Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15
I want column I to be covered by black until typing a date in column A. I
want column A to have no background unless the date typed into it is more
than 12 months old. I do not want column A covered by black though. If I
change the 1311 in column I to zero then I lose my formula.

"T. Valko" wrote:

Ok, let's get this straightened out...

formula in column I the column shows
1311 all the way down
formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m")

That means the cells in column A are empty. An empty cell evaluates to 0 and
0 is the date serial number for 1/0/1900 (actually, the true value is Dec 31
1899 but Excel doesn't recognize dates before 1/1/1900. The 0th day of a
month refers to the *last* day of the previous month. There's a little more
to it than that but I don't want to go off into an entirely different
topic!).

So, change your formula to:

=IF(COUNT(A5),DATEDIF(A5,NOW(),"m"),"")

Copy down as needed.

OK, now your formatting...

You actually need to apply cf to both ranges, column A and column I.

If you want to keep column I "blacked out" until a date entry is made in
column A...

Select the range of cells of interest in column I. Let's assume the range is
I5:I20.
Goto the menu FormatCellsPatterns tabselect BlackOK

With the range still selected, now apply the cf...

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=COUNT(A5)
Click the Format button
Select the Patterns tab
Select No Color
OK out

Now, apply the cf to column A:

Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15

Ok, what should happen if the cell value is greater than 15? I'm going to
take a guess and say you want the cell shaded GREEN.

Select the range in question. Let's assume the range is A5:A20

Conditional Formatting
Condition 1
Formula Is: =I5=12
Patterns tabselect a shade of RED
OK

Click the Add button

Condition 2
Formula Is: =AND(I5=13,I5<=15)
Patterns tabselect a shade of YELLOW
OK

Click the Add button

Condition 3
Formula Is: =AND(COUNT(I5),I515)
Patterns tabselect a shade of GREEN
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...


"T. Valko" wrote:

Let's assume you want to format the range A1:A5 if the corresponding cell
in
I1:I5 =12. Assuming the values in column I are numeric integers.

Select the range A1:A5
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(I1),I1=12)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...
I have column A that I want to turn the back ground red if the number of
months is = 12 in column I. If the number of months is =< than 11 I
don't
want a back ground. I can either get it to turn red for both instances
or
stay the same for both instances. Please help!!!!

It works but I still have a problem. The A column is stay red because when
I put my formula in column I the column shows 1311 all the way down and I
don't know why. My formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m") and the conditional formatting is:
Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15
I want column I to be covered by black until typing a date in column A. I
want column A to have no background unless the date typed into it is more
than 12 months old. I do not want column A covered by black though. If I
change the 1311 in column I to zero then I lose my formula.



THANK YOU VERY MUCH!!!

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...


"T. Valko" wrote:

Ok, let's get this straightened out...

formula in column I the column shows
1311 all the way down
formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m")

That means the cells in column A are empty. An empty cell evaluates to 0
and
0 is the date serial number for 1/0/1900 (actually, the true value is Dec
31
1899 but Excel doesn't recognize dates before 1/1/1900. The 0th day of a
month refers to the *last* day of the previous month. There's a little
more
to it than that but I don't want to go off into an entirely different
topic!).

So, change your formula to:

=IF(COUNT(A5),DATEDIF(A5,NOW(),"m"),"")

Copy down as needed.

OK, now your formatting...

You actually need to apply cf to both ranges, column A and column I.

If you want to keep column I "blacked out" until a date entry is made in
column A...

Select the range of cells of interest in column I. Let's assume the range
is
I5:I20.
Goto the menu FormatCellsPatterns tabselect BlackOK

With the range still selected, now apply the cf...

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=COUNT(A5)
Click the Format button
Select the Patterns tab
Select No Color
OK out

Now, apply the cf to column A:

Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15

Ok, what should happen if the cell value is greater than 15? I'm going to
take a guess and say you want the cell shaded GREEN.

Select the range in question. Let's assume the range is A5:A20

Conditional Formatting
Condition 1
Formula Is: =I5=12
Patterns tabselect a shade of RED
OK

Click the Add button

Condition 2
Formula Is: =AND(I5=13,I5<=15)
Patterns tabselect a shade of YELLOW
OK

Click the Add button

Condition 3
Formula Is: =AND(COUNT(I5),I515)
Patterns tabselect a shade of GREEN
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in message
...


"T. Valko" wrote:

Let's assume you want to format the range A1:A5 if the corresponding
cell
in
I1:I5 =12. Assuming the values in column I are numeric integers.

Select the range A1:A5
Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNT(I1),I1=12)
Click the Format button
Select the Patterns tab
Select a shade of RED
OK out

--
Biff
Microsoft Excel MVP


"GrouchyMammy" wrote in
message
...
I have column A that I want to turn the back ground red if the number
of
months is = 12 in column I. If the number of months is =< than 11 I
don't
want a back ground. I can either get it to turn red for both
instances
or
stay the same for both instances. Please help!!!!

It works but I still have a problem. The A column is stay red because
when
I put my formula in column I the column shows 1311 all the way down and
I
don't know why. My formula in the formula bar is:
=DATEDIF(A5,(NOW()),"m") and the conditional formatting is:
Condition 1:
Cell value is equal to 12 format is red
Condition 2:
Cell value is between 13 and 15 format is yellow
Cell value is greater than 15
I want column I to be covered by black until typing a date in column A.
I
want column A to have no background unless the date typed into it is
more
than 12 months old. I do not want column A covered by black though. If
I
change the 1311 in column I to zero then I lose my formula.



THANK YOU VERY MUCH!!!