Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
BigMac
 
Posts: n/a
Default Macro/conditional formatting

Every day I receive a tab delimited file that I open in
Excel. The file contains a date field and the first record
is always in row 6, with the date in cell E6. Each tab
delimited file I receive contains a different number of
records. When I import the file, I always make sure that
the Date field is imported as a date field rather than a
General field.

After I import the tab delimited file, I sort E6:E???
(number of records varies) in ascending order. Then I
manually identify records that fall into the less than 30
day from today range and color those cells green, then I
identify those records in the 31 - 60 range and color
those yellow, and finally, those records greater than 61
days I color red.

I then save the file as an .xls file.

It seems to me that I should be able to automate the cell
coloring process each time I import a file, but don't know
how.

Your help is greatly appreciated.
  #2   Report Post  
Earl Kiosterud
 
Posts: n/a
Default

Mac,

You can use conditional formatting. Format - Contitional formatting. It's
not necessary to have them sorted for that.

As for automating it, if you're truly importing (not opening), you can have
the import specs, conditional formatting, all set up in a workbook. Then
you just take a copy of the file, do the import, and you're ready to go.
Read up on importing vs. opening at www.smokeylake.com/excel. "Text files
and Excel."
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"BigMac" wrote in message
...
Every day I receive a tab delimited file that I open in
Excel. The file contains a date field and the first record
is always in row 6, with the date in cell E6. Each tab
delimited file I receive contains a different number of
records. When I import the file, I always make sure that
the Date field is imported as a date field rather than a
General field.

After I import the tab delimited file, I sort E6:E???
(number of records varies) in ascending order. Then I
manually identify records that fall into the less than 30
day from today range and color those cells green, then I
identify those records in the 31 - 60 range and color
those yellow, and finally, those records greater than 61
days I color red.

I then save the file as an .xls file.

It seems to me that I should be able to automate the cell
coloring process each time I import a file, but don't know
how.

Your help is greatly appreciated.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
conditional formatting blank cell TREK5200 Excel Discussion (Misc queries) 1 December 6th 04 03:23 AM
Date Formatting Neil Excel Discussion (Misc queries) 6 December 4th 04 07:57 PM
Adding more than three Conditions to 'Conditional Formatting' David McRitchie Excel Discussion (Misc queries) 1 November 27th 04 07:03 PM
Formatting sheets C Tate Excel Discussion (Misc queries) 2 November 27th 04 04:57 PM
Cells losing formatting BdgBill Excel Discussion (Misc queries) 1 November 26th 04 09:03 AM


All times are GMT +1. The time now is 01:06 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"