Color coding "If, then" formulas

  • Thread starter Thread starter ehjensen
  • Start date Start date
E

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
 
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?


Ed Trotter said:
Try Conditional formatting.
Click to expand...
 
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
 
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


Dave R. said:
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?
Click to expand...
 
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
 
Back
Top