Color coding "If, then" formulas

[ehjensen]

Hi,

I really need some help using an advanced function like, "If, then".

What I need to do is create a spreadsheet that has test requirement
and a need date. If the need date is within, say, 30 days, the entir
column turns yellow (Caution); if the date is within 10 days, th
entire column turns red (Warning).

Is there a formula to use that can accomplish this in Excel. I'm usin
Excel 2000.

Thanks,

Eri

 

[Ed Trotter]

Try Conditional formatting.

 

[Dave R.]

Of course that comes to mind-
Just wondering, can that change the color of an entire column as he's
looking to do, or just individual values that fit the conditon?

Try Conditional formatting.

 

[firebird96]

Erik

Under the Format menu there is a selection named 'Conditional
Formatting' Once you highlight the desired cell, or cells, click the
'Conditional Formatting' selection to expose the expression builder.
You will note that you can set conditions using the current cell data,
or a formula referencing other cells. You can build up to 3ea
conditional statements against each cell or array.

Kevin

 

[David McRitchie]

Yes, but I don't have a feel for exactly what cells might change
and where to comparison date is
for the column to change it's color, so it is not a sure thing.

Location of need date(s)
Location of comparison date
Column(s) to be colored.

If none of the above are plural then there would be no problem
at all.

My page on Conditional Formatting is
http://www.mvps.org/dmcritchie/excel/condfmt;

If it can't be done with C.F. then an event macro would be the
next step. event.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Of course that comes to mind-
Just wondering, can that change the color of an entire column as he's
looking to do, or just individual values that fit the conditon?

 

[cturkeys]

What if you have more than three conditions?

 

[Frank Kabel]

Hi
conditional format only accepts 3 conditions though you have a fourth
if you include the default format.
If you only want to apply different FONT colors based on NUMBERS, you
can define up to 6
different styles. See:
http://www.mcgimpsey.com/excel/conditional6.html
for instructions how to do it

For everything else you'll need VBA code (e.g. process the
worksheet_change event and apply your format based on the cell values).
The following will color the text in A1 depending on its numeric value:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A100")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case 1: .Font.ColorIndex = 3
Case 2: .Font.ColorIndex = 10
'etc.
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Frank