I have an excel spreadsheet which will calculate to do events (15) by
a starting date. As I input an animals id number and birth date, the
various weigh dates are calculated. I would like the calculated dates
to change color (either the background or the number) indicating past
due, current (5 day window), future and completed (value entered in
adjacent column). I would like to be able to print the items from
each category. I will be happy to provide a copy of the spreadsheet
as written. |
Request for Question Clarification by
hummer-ga
on
02 Jul 2004 10:51 PDT
Hi i_am_at_home,
The easiest way to accomplish what you want is to use "Conditional
Formatting". There is a nice explanation of it at the following
website. Unfortunately, there is a limit of three formats, but you
need four (past due, current, future, & completed).
"There is a limit of 3 conditional formats that can be applied to any cell."
"If you need to extend this to more than 4 formats, you will have to
write a macro in VBA."
http://www.mrexcel.com/tip054.shtml
Are you interested in Conditional Formatting or asking for someone to
write a Macro for you?
Regards,
hummer
|
Clarification of Question by
i_am_at_home-ga
on
03 Jul 2004 10:43 PDT
Hello Hummer-ga,
I am not exactly sure I understand what a macro is. If it does
something automatically when I open my file then I believe I would
like the macro. I will attempt to describe my needs in greater
detail. Here is part of my spreadsheet:
Cell A2 input a number
Cell B2 input a number
Cell C2 input a date
Cell D2 input a number
Cell E2 calculated formula =IF(C2="","",C2+14)
Cell F2 input a number
Cell G2 calculated formula =IF(E2="","",E2+14)
Cell H2 input a number
Cell I2 calculated formula =IF(G2="","",G2+14)
Cell J2 input a number
Cell K2 calculated formula =IF(I2="","",I2+14)
Cell L2 input a number
Next I have an example:
Let us just work with one row (number 2) and columns C through F. I
fill in manually A2 through D2. Cell E2 generates a date using the
formula =IF(C2="","",C2+14).
Premise --- These will never change ---
Input to C is Feb 15. This value will not change.
Input to D is 8. This value will not change.
Calculated date in E is Feb 29. This value will not change.
Changes ---
Todays date changes every day.
F2 will be filled one time and never change. ---
Today progresses AND cell F2 is blank
Today is Feb 15. Cell E2 is colored Orange.
Today is Feb 26. Cell E2 is colored Orange.
Today is Feb 27. Cell E2 is colored Blue.
Today is March 2. Cell E2 is colored Blue.
Today is March 3. Cell E2 is colored Red.
Today is May 23. Cell E2 is colored Red.
Cell F2 is filled in.
Today is ANY date. Cell E2 is colored green and never changes.
The only cell to change color is E2. No other rows or cells will
change color based on the C2, D2, E2 or F2 data or today's date. The
other calculated date cells will each need to color change and are to
be ignored in this example only. The other calculated date cells and
their adjacent number inputed cells are included in the final supplied
macro.
I hope this will make the result I need clear to you.
Thank you.
|
Request for Question Clarification by
hummer-ga
on
03 Jul 2004 15:22 PDT
Hi i_am_at_home,
I'm not the person to help you with MACROS, but in the meantime, give
Conditional Formatting a try. If it works, it would be ever so much
easier (and you could make changes to it easier too). Try the
following and see how it goes. It may need some tweaking but you'll
get the idea. Once you get E2 to work, you can copy the formatting for
the rest of the column.
Select E2
FORMAT / CONDITIONAL FORMATTING
"Cell Value Is" change to "Formula Is"
FEB 15
Enter: =(E2-TODAY())>0
Click FORMAT button and choose ORANGE. Click OK.
FEB 27
Click the Add button
Change "Cell Value Is" to "Formula Is"
Enter: =(E2-TODAY())>12
Click the Format button and choose BLUE. Click OK.
MARCH 3
Click the Add button
Change "Cell Value Is" to "Formula Is"
Enter: =(E2-TODAY())>16
Click the Format button and choose RED. Click OK.
MAY 23
Click the Add button
Change "Cell Value Is" to "Formula Is"
Enter: =(E2-TODAY())>97
Click the Format button and choose GREEN. Click OK.
I'm just posting this to get you started - perhaps another researcher
will be able to help you out better than I.
Regards,
hummer
|
Clarification of Question by
i_am_at_home-ga
on
04 Jul 2004 07:28 PDT
Hi dreamboat-ga,
Here goes ;-)
Attachments (spreedsheet) cannot be sent by Googls answers. That is
why I sent the spreedsheet in the body of the answer.
The cells containing date data change color.
As time passes and the date in the computer progresses.
I want the display of E2, G2, I2 and K2 to change color.
The colors are described in my example.There are thousands of cells
containing dates.
Today is the computers internal date. It is never inputed to the spreedsheet.
Should you set up a spreedsheet with the formulas as I gave them, then
you will have to input a date in cell C2 and the other fill in.
I need a MACRO.
What I am doing is making a work schedule for the farm. There are
hundreds of animals which need different things on each day. I want
to be able to print a color subsection of the sheet to provide a list
of animals which need a particular thing accomplished. When I look at
a sheet the colors stand out.
I need a programer.
Thank you
|
