After a cell changes colour when a date is near

When the cell changes to a specific colour say a12 goes red when a date is 
within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a 
persons name is in a1) be put on a different worksheet and the same for each 
row so i can have a list of names and assessments on a seperate worksheet to 
print off

so it would look like this

A N OTHER      FDA              02-JAN-06
A BODY           SUMMARY     02-JAN-06


0
cityfc (14)
1/10/2006 12:35:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
355 Views

Similar Articles

[PageSpeed] 14

You could use a filter to do that.  Select your table, then use Data Filter...  Auto Filter.  Then 
use Custom on your date field, and some combination of "Less than or equal to", " Greater than or 
equal to"  etc. will allow you to only show the rows with the dates that you are interested in, 
prior to printing.

HTH,
Bernie
MS Excel MVP


"cityfc" <cityfc@discussions.microsoft.com> wrote in message 
news:37C3C1D5-349F-4BD0-9628-15DB2F1954D4@microsoft.com...
> When the cell changes to a specific colour say a12 goes red when a date is
> within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a
> persons name is in a1) be put on a different worksheet and the same for each
> row so i can have a list of names and assessments on a seperate worksheet to
> print off
>
> so it would look like this
>
> A N OTHER      FDA              02-JAN-06
> A BODY           SUMMARY     02-JAN-06
>
> 


0
Bernie
1/10/2006 3:03:17 PM
Thanks for the quick responce 
that would be great if it was just me who dealt with the matrix but other 
users who aren't to fluent deal with it and i was hoping if there was a 
formula, macro etc that could be inserted that would be great and they would 
only have to press print

"cityfc" wrote:

> When the cell changes to a specific colour say a12 goes red when a date is 
> within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a 
> persons name is in a1) be put on a different worksheet and the same for each 
> row so i can have a list of names and assessments on a seperate worksheet to 
> print off
> 
> so it would look like this
> 
> A N OTHER      FDA              02-JAN-06
> A BODY           SUMMARY     02-JAN-06
> 
> 
0
cityfc (14)
1/10/2006 3:38:03 PM
You could use a macro like the one below, which would filter the table starting in cell A1, based on 
dates in column B, showing values from today until next week (the  + 7 part).  It will then print 
the table. Assing the macro to a button and the user can just press the button to filter and print.

Post back if you need help getting the specific date range that you want.

HTH,
Bernie
MS Excel MVP

Sub FilterAndPrintOneWeek()

   Range("A1").CurrentRegion.AutoFilter _
   Field:=2, Criteria1:=">=" & Date, Operator:=xlAnd, _
       Criteria2:="<=" & Date + 7
   ActiveSheet.PrintOut
   Range("A1").CurrentRegion.AutoFilter

End Sub


"cityfc" <cityfc@discussions.microsoft.com> wrote in message 
news:0786A0E1-C08F-49B0-8EA1-55C046F7DFA0@microsoft.com...
> Thanks for the quick responce
> that would be great if it was just me who dealt with the matrix but other
> users who aren't to fluent deal with it and i was hoping if there was a
> formula, macro etc that could be inserted that would be great and they would
> only have to press print
>
> "cityfc" wrote:
>
>> When the cell changes to a specific colour say a12 goes red when a date is
>> within 30 days (a12 is a date an assessment is due) can a12 and a1 (which a
>> persons name is in a1) be put on a different worksheet and the same for each
>> row so i can have a list of names and assessments on a seperate worksheet to
>> print off
>>
>> so it would look like this
>>
>> A N OTHER      FDA              02-JAN-06
>> A BODY           SUMMARY     02-JAN-06
>>
>> 


0
Bernie
1/10/2006 3:58:03 PM
Reply:

Similar Artilces:

How can i change this VBA project According to Indian Numeric
Sir/Madam I got a VBA project from MCServices, But problem is How i should modify this to display This Numeric Value like - 2551525.35 It should display in text = Twenty Five Lac Fifty One Thousand Five Hundred Twenty Five Rs and Thirty Five Paise. I m sending Herewith my VBA project. Pls change necessary and help me. thanks Rao Ratan Singh Option Explicit 'Main Function Function SpellNumber(ByVal MyNumber) Dim Dollars, Cents, Temp Dim DecimalPlace, Count ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = &...

remember path when linking many GIFs to cells?
When I link a number of GIF images residing in a different directory to Excel 2002 cells, the program does not remember the last directory it looked in when a new link is entered. Is there a way to set up a default path for this boring operation? Thanks, z.entropic ...

How do I change the format of how my active cell in Excel is view.
I sometimes have trouble locating which cell is active in my Excel worksheet. I would like to be able to change it to something other than just a heavy black border. Here is one way that highlights the row and column of the activecell. Private Sub WorkSheet_SelectionChange(ByVal Target As Range) Cells.FormatConditions.Delete With Target.EntireRow .FormatConditions.Add Type:=xlExpression, Formula1:="TRUE" .FormatConditions(1).Interior.ColorIndex = 35 End With With Target.EntireColumn .FormatConditions.Delete .FormatConditions.Add T...

format changes on pivot table although preserve formatting is che.
Post the question the body of the message, subject lines get truncated -- Regards, Peo Sjoblom "Aannd" <Aannd@discussions.microsoft.com> wrote in message news:282F34F1-42CA-4F0A-8A62-A71C64E30741@microsoft.com... > ...

Determine if another cell is hidden in a Formula
Hi! I would like to display a special message in a cell when another cell is hidden. Is it possible? Something like (in cell A2): =If(IsHidden(A1), A1, "") I guess I could do it with a user function... but if I use that in a lot of cells (hundreds) it might significantly slow down the refresh speed... Any thoughts ? J Whales ...

Display Sheetname in a cell
I'd like to display a worksheet name "with the body" instead of "in the margins" (ie, using Header / Footer definitions). Is there a built-in mechanism for picking up the worksheet name in a formula? I tried putting Function GetSheetName() As String GetSheetName = ActiveSheet.Name End Function in a code module and " = GetSheetName() " in a cell; but quickly discovered that <F9> doesn't cause the user defined function to re-evaluate. Suggestions? -- Clif Try this: =MID(CELL("filename",A1),FIND("]",CELL("fil...

Hyperlink directly to a cell in a worksheet
Hi there, I'd like to hyperlink directly to a cell in a spreadsheet (spreadsheet opens to a specific cell selected). The link I'm using opens the spreadsheet, but it goes to the same cell everytime. Is there a way to do this? Thanks in advance for any help. Here's what the link I'm using looks like: www.myserver.com/Payroll_Risks_&_Controls.xls#='Risk Scores 1, 2, or 3'!$A$27 Risk Scores 1, 2, or 3 is the name of the worksheet and A27 is the cell I want to go to. ...

drag and drop problem with cell references
i have a cell with a formula in one worksheet that references another cell. the formula looks like this: =IF((ISBLANK('Draft'!$D25)),"",'Draft'!$D25) where 'draft' is another worksheet in my workbook. the formula works fine, it looks empty if there is nothing in the referenced cell('Draft'!$D25), otherwise it mirrors the content of that cell. If i type something in the referenced cell('Draft'!$D25), it still works great. But if i drag something to the referenced cell than the formula gets screwed up and replaces the $D25, with #ref. I am gue...

Need help removing characters in multiple cells
I have an excel 2007 sheet that has over 300 cells in a column that have a name of a store plus a number between paranethesis. Is there a one step way I can remove the paranethesis? Here is an example: K-Mart (ABC#73846365) and I want to have just the following K-Mart Each cell has a different store name and set of numbers within the paranethesis. Thanks Select the range to fix. Edit|replace what: _(* (spacebar, open paren, asterisk) with: (leave blank) replace all It looked like you'd want to remove the space character after the t in k-mart. "<----- Mardm...

fonts are changing and text disappears
Hello when i open outlook 2003 all my maps on the left side of the screen are looking well and normal. But after a wile the font changed or a piece of the name/text disappear. Very stange i think. When i close outlook and turn it back on, everyting is back to normal but not for long. ...

Changing Fonts
Using Publisher 2003 with Office SP2. I have installed 3 new fonts (.ttf files) through Control Panel - Fonts - Install New Font and they now appear in the list in Control Panel - Fonts. I want to use these fonts in a publisher document. How do I get the new fonts to appear in my list of fonts in publisher? Publisher has been reopened since installing the new fonts and I have also restarted. Any help would be great! Thank you! REW wrote: > Using Publisher 2003 with Office SP2. > I have installed 3 new fonts (.ttf files) through Control Panel - > Fonts - Install New Font and the...

Changing Free/Busy Publishing Time
I wanted to change our publishing time from two months to 12 months. In order to do this I implimented a group policy that changed this time. The group policy seems to be effective in changing that value, however when it comes to scheduling meetings, only two months are visible. The only way I have found to rectify this is to run Outlook with a /cleanfreebusy switch once. However, it doesn't seem practical to do this across a large number of users. Is there any way I can do this server side to acheive the same effect? Thanks! On 28 Jul 2006 09:40:46 -0700, JamesSKemp@gmail.com wrote: &...

Signature format changes in replies with Outlook 2007
Hi We are using Outlook 2007 and are finding that our signatures will sometimes change font and spacing in reply messages. Is there a way to fix this? Thanks ...

Linked Cell Property In Activex controls
Can someone point me to an example showing how this property can be used, linking, as an example, an option button to a specific cell? Say if I wanted "1" to appear in cell B2 of the worksheet if the option button is clicked KG, The option button puts TRUE or FALSE into its Linked Cell. You can get 1 or 0 out of that by referring to it with double negation operators. = --A1 -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "KG" <KG@discussions.microsoft.com> wrote in message news:924E6B40-6040-4016-B75B...

Changing Information
Using Excel and Access 2003 on an XP SP2 machine. Is there a way to produce a pie chart from an Access query when you will not always have the same fields return results to be plotted? (In case that was as clear as mud to you as it was to me, I'll try to explain.) I know how to return reqults from a query to a spreadsheet and produce a chart from there. What has me stumped is what to do when the fields change. For instance, I'm trying to produce a pie chart that will show how many of several different categories of data occur. However, depending on what transpires during a repor...

CFtpFileFind return null creation date
Hi All I am trying to list the URL and timestamp of some files on a server in a list box in a simple dialog using FTP. The code is as follows: void CFTPTestDlg::OnTest() { CListBox* pbox = (CListBox*)GetDlgItem(IDC_LB_FILES); pbox->ResetContent(); // Create session object to initialise libs CInternetSession sess(_T("My Test")); // Declare a FTPConnection CFtpConnection* pftpConn = NULL; try{ pftpConn = sess.GetFtpConnection("ftp.,mytestsite.co.uk", "mylogon","mypassword"); if (!pftpConn->SetCurrentDirectory("/Testdir/T...

help with range lookup and date criteria
Hello, I have a list of doctors (column A) that are each on-cal through a number of days. My argument is: if the date value of B1 an C1 is within the current date, then repeat the value of A1 -- James Spaldin ----------------------------------------------------------------------- James Spalding's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2567 View this thread: http://www.excelforum.com/showthread.php?threadid=39088 hi, James ! > ... a list of doctors (column A) that are each on-call through a number of days. > ... argument is: if the date value ...

extract year from Date Value
Good morning, Could someone help me extract the year portion from a date value such as this 11/20/2009? Thanks in advance, Mike With the Date in A1, place =YEAR(A1) in B1 Takeadoe wrote: > Good morning, > > Could someone help me extract the year portion from a date value such > as this 11/20/2009? > > Thanks in advance, > > Mike ...

Removing Blank Cells #3
I would like to know if there is a way to remove the blank columns and rows from a worksheet. There really is no need for this many blank rows in the workpaper I am working on and I assume it contributes to the overall size of the spreadsheet. Thanks in advance. Hi you can't remove them (you can hide them if you like9. Also they do not add up to the size of your worksheet 8at least as long as your used range is correct) -- Regards Frank Kabel Frankfurt, Germany Jeff Mortenson wrote: > I would like to know if there is a way to remove the blank > columns and rows from a works...

how do I add a 'web address' to a cell in excel with a hyperlink?
how do I add a 'web address' to a cell in excel with a hyperlink? I am a new student to computers, and part of a test is to insert a hyperlink to a cell in excel linking to a web address. I have searched computer for any 'Hyperlink' connection words, but I think it not on here, Is that possible, although I have top of range computer. Anyone with a solution? I be ever so greatful. in xl2002 ctrl+k>in the link to select existing web page>either type in (at the bottom) or point to the url on your browser>enter -- Don Guillett SalesAid Software dguillett1@austi...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

Help with Date/Time Function
I am working with date/time functions trying to figure out how many hours were spent doing a job. I have a start time and a finish time. But I need to subtract out any time between five pm and six am and weekends from the times. These would be times that no one was actually working on the job. For example I have 4/5/2005 18:23 4/6/2005 17:04 Could someone give me a clue as to how this might be done? Thanks, Steve Monczka smonczka@hotmail.com It's not a trivial thing, here's an example http://www.cpearson.com/excel/DateTimeWS.htm or here http://tinyurl.com/cbphn -- R...

Suppres Zero or empty Cell value in a line graph
Hi I'm using Office 2007. I have two charts using data from the same sheet. The second graph is a copy of the first. In the first graph, the empty and zero value cells are not displayed. In the second graph, the zero value cells is displayed (draged to zero) although the option "connect points with line" is checked. The strange thing: if I change the Y-data to another part of the sheet, it is no longer dragged to zero. Even if the cell is empty it's still dragged to zero A formula that returns "" is not an empty cell, it's a formula (or it's a small ...

Change column color in chart when column value is over/under goal
No use of VBA or macros expected. It is believed to be Excel chart feature. Any ideas on how to change column colors (Red/Green) if value exceeds or meets the preset goal. Eg. if goal is 4% - anything at or above 4 should show green and under 4 should be red. Assuming your data is in A2:A20 B2: =IF(A2>=4,A2,NA()) C2: =IF(A2<4,A2,NA()) copy B2:C2 down, add some labels to B1:C1, then chart B1:C20. This will give you two series, one for the aboves, one for the rest. Select each data series, right click, choose format, and set the colour as required. -- --- HTH Bob (there's ...

Past calendar dates losing their bolding
I know that the calendar has the current month, previous month and up to 10 months in the future to have the dates bolded if there are appointments there, but I have several clients who want more dates in the past - at least a year - to be bolded. Any way to do this? Mari (tulipmari.nospam@shaw.ca) remove the .nospam to respond to my email -- Sorry, no. it goes back just 2 months. -- Diane Poremsky [MVP - Outlook] http://www.poremsky.com - http://www.cdolive.com Outlook Tips: http://www.outlook-tips.net/ Expert Zone http://www.microsoft.com/windowsxp/expertzone Search for answers: http:/...