conditional formatting in excel 2003

[GrouchyMammy]

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]

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 Format>Conditional 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

 

[GrouchyMammy]

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 Format>Conditional 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




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]

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 Format>Cells>Patterns tab>select Black>OK

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

Goto the menu Format>Conditional 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 tab>select a shade of RED
OK

Click the Add button

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

Click the Add button

Condition 3
Formula Is: =AND(COUNT(I5),I5>15)
Patterns tab>select a shade of GREEN
OK out

 

[GrouchyMammy]

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


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 Format>Cells>Patterns tab>select Black>OK

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

Goto the menu Format>Conditional 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:


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 tab>select a shade of RED
OK

Click the Add button

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

Click the Add button

Condition 3
Formula Is: =AND(COUNT(I5),I5>15)
Patterns tab>select a shade of GREEN
OK out

--
Biff
Microsoft Excel MVP





THANK YOU VERY MUCH!!!

 

[T. Valko]

You're welcome. Thanks for the feedback!