auto format cells to highlight only when data is entered
Is there a way to automatically have cells highlight when data is entered. I have a column that only receives occasional data and I would like it to stand out. I'm very new to excel. Thanks
Try "Conditional Formatting:.
Select the cells in question, then:
First window - "Cell value is"
second window - "Not equal to"
Third window, two double quotes - ""
Choose your formats, then <OK>.
Please keep all correspondence within the Group, so all may ...POPULATING DATES IN A CALENDAR
I have 7 worksheets with a list of trucks from different plants that
need painting throughout the year.
I wish to enter a date next to the truck number in one worksheet and
then have that date be colored (or better yet have the truck number
appear) in a 12 row 31 column calendar in another worksheet. My goal is
to quickly see which dates I have open to schedule our paint shop to
paint a truck.
Thanks in advance for any help.
...Conditional Formatting for Changes to cells
I want to use either conditional formatting or change tracking to change the
font color on any changes made to a spreadsheet by a user. Just one user
will be using each spreadsheet but the changes are then submitted to someone
for data entry into the big iron.
Right-click the sheet tab, select View Code, enter this:
Private Sub Worksheet_Change(ByVal Target As Range)
Target.Interior.Color = vbRed
"Dick Stapleton" <email@example.com> wrote in message
>I want to u...repost; Automatically updating formulae in multiple sheets
I list sales of 20+ products each week for the year
A2-A23 are the products and B-BA are the colums for each week
I have multiple sheets that have the same sales results in the same format
foreach previous year 2004;2003;2002;2001;2000
Each week I want to compare total sales in the year to date i.e weeks 1-20
with weeks 1-20 in each previous year. so next week it will be 1-21.
I have on each sheet a cumulative total for each of the 20 products in the
year to date i.e Cell A25=SUMB2:BA2 and Cell A26=SUMB3:BA3 and so on for
each product. This means each week I have to alter the formula in C...From and to date
In the report wizard it gives the option to group by date but not a start and
end date. Is there a way to do this through the wizard or does the report
have to be wriiten manually.
The field is date time format
Thanks for any help
Not sure what you mean by "written manually" ...
If you base your report on a query, rather than a table, you can use
selection criteria in the query to limit records to a date range.
Microsoft Access MVP
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or d...Date Format #3
I have a spreadsheet that I want the date to be formatted
as day/month/year (13-09-03). I go to format cells and
choose that format for my column. When I type in the date
as 13/09/03 it will not convert the date to the correct
format. However if I type in 09/13/03 Excel will convert
to the proper format. Does this have anything to do with
my regional settings in Windows. Would I have to change
the setting to day/month/year in windows. I am using Excel
Thanks in advance for your help.
Number format affects display only, not how XL parses an entry.
To change the p...Repost
I sent his message earlier but have not been able to view it from here so I
apologize if it has already been answered.
I had heard that the refresh was released sometime last week but have
received no notification from MS about it. When will I know? Will MS send an
email to everyone that purchased the Beta 2 Kit? I have clicked "Check for
Updates" but there is no information there. All I get is "All your office
products are up to date". Thanks for reading.
I have the following URL to view, nothing more.
I rea...Formatting of Data Labels in Histograms
I have used histograms extensively in MS2003 but have encountered a broblem
using MS2007. I have a spreadsheet that shows dollar amounts of contracts
being awarded during certain months over a three year period. Some months
more than one contract is awarded, others have one or none. Therefore, my
histogram is a stacked column type. My X-axis shows months and my Y-axis
dollars. The problem I am encountering is with the data labels which I put
centered on the column. The dollar amounts show correctly but for some
reason there is a zero at each interval of the X-axis, even for months...sequential date
Is it possible to have in sequence a date value
I require four dates the same before sequencing the next date, so
In A1 : initial date
In A2 :
copy down as needed.
> Is it possible to have in sequence a date value
> I require four dates the same before sequencing the next date, so
> 03/01/1...undoing conditional formatting
Hello and great day today. I use conditional formatting to find an
error(s) in many rows and columns when my grand totals aren't matching
both down and across. I might have 15 columns and 45 rows with data and
currency listed. I must make sure each row across matches to a grand
total and same down with the columns.
The conditional formatting is great for high-liting discrepancies But
it also highlites rows that are not really errors when put together
with another row. So I fix the row with an actual error but how do I
get rid of the highlite on the rows which are not really errors. I
can...attempting to format a color pattern in tab 3, based tab 2 and tab
I have many cells of data on tab 1 (Actual data), it is replicated on tab 2
but with a different data set (Budget data), and tab 3 is the difference
between those first 2 tabs and I want each of the cells on tab 3 to evaluate
against tabs 1 and 2. In tab 3, if the same cell, say c3, in tab 1 is less
than ABS of 1, AND the same cell in tab 2 is greater than ABS of 1, then the
same cell, say C3, on tab 3 I want a color pattern of Blue, else no color
Thanks for your help.
You can do this with Conditional Formatting ("Use a formula to determine
...Counting dates within a range
I have a large database of ~10,000 dated and timed entries. Each entry
represents the date and time of a hospital admission. I would like to know
for each entry, how many other events took place within 24 hours. I am trying
to figure out how many hospital admission happened in the 12 hours prior to
and after each admission to estimate the daily admission rate more accurately
than just by calendar day.
I have tried
Where AV is the column of all dates, and AV2 is the date in question. This
fo...Date Intervals from dates
I've created a query that lists the dates between a start date and an
end date. From the list of dates, i need to find the intervals
between the dates and then find the average of that.
How do I get the intervals in a column in the query.
Subquery basics: Get the value in another record
The example is for the previous meter reading so you can subtract from the
current value, but it works the same for dates.
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http:/...How do I change the comment default format to include date?
In Excel 2007, I want to change the default format for comments, so that the
date the comment was added is included in the comment as well as the name of
the user who added the comment.
Don't think that you can, the username is all you get, the rest you add
"frj5000" <firstname.lastname@example.org> wrote in message
> In Excel 2007, I want to change the default format for comments, so that
> date the comment was added is included in the comment as wel...Grouping with Outlook 2003; Conversation ordering, dates
We're (just now) moving to Outlook 2003 at my business and thus I can
no longer use Thunderbird that I've been using for quite a while.
Outlook's grouping features seem convenient for the most part, but I
can't seem to duplicate the threaded message behavior that Thunderbird
I can group Outlook messages by Conversation, but only in
ascending/decending order, and unfortunately this is in alphabetical
I want to keep my messages in conversation grouping, but want the most
recently updated conversation at the top of my list. The way I have it
figured out so far, if a...Hide timeline date
I'd like to hide the text of the date that appears in the text above the
timeline icon. I want the text to appear, but with so many events on my
timeline I want to reduce the clutter. How can I do this?
On Fri, 22 May 2009 09:19:00 -0700, MikeM
>I'd like to hide the text of the date that appears in the text above the
>timeline icon. I want the text to appear, but with so many events on my
>timeline I want to reduce the clutter. How can I do this?
Do you mean the milestone shapes? If so then I've put some edited
ve...showing dates, e.g. 1st, 2nd, 3rd with st, nd, rd in superscript
I've recently started using Word 2007. I prefer to write dates in the form
1st January, rather than January 1. My previous edition of word always
automatically gave st, nd and rd as smaller font superscripts or suffixes. I
can't find a way of getting Word 7 to do that as a default.
See the Date Fields with Ordinals section of the following page of fellow
MVP Graham Mayor's website:
Hope this helps,
Doug Robbins - Word MVP
Please reply only to the newsgroups unless you wish to obtain my services on
a paid p...Date field
I have a date field which has a range of dates from 1850 - 1930
Each entry has dd mm yyyy
how do I
1) format all fields to read e.g. dd Mmm yyyy
2) sort successfully on all records
Select the range you want to format.
In the Number Tab you will find the field "date" and on the right side you
will find the format you need.
Hope it helps.
"Bob Matthews" wrote:
> I have a date field which has a range of dates from 1850 - 1930
> Each entry has dd mm yyyy
> how do I
> 1) format all fields to read e.g. dd Mmm yyyy
> 2) sort successfully ...Wrong Date
I dont know if anyone has asked this question before as i am new to
this. I have Outlook 2000 and when I receive an email it is showing
the wrong time by an hour but the date is correct, can someone please
help me to ressurect this
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
"forro5" <email@example.com> wrote in message
> I dont know if anyone has asked this quest...Office Outlooks dont show embeded images in HTML format mail
Why does in Outlook Express I see image embeded
in html format email and in Office Outlook 2000 or 2003
there is no image only small cross but image is sended with email?
Why this email source below look different in Outlook Express and
in Outlook with situation described above?
Content-Type: multipart/related; boundary="X----1897694375454659af00c36"
Date: Mon, 30 Oct 2006 21:02:02 +0100 (CET)
This i...How do I subtract a date from a date for a sum of total years?
I am trying to calculate how many years an employee has worked. I have start
date and current date. What is the formula to return years worked?
If the current date is in A1 and the start date is in B1, then
=year(A1)-year(B1) formatted as general will return the number of years
> I am trying to calculate how many years an employee has worked. I have start
> date and current date. What is the formula to return years worked?
=DATEDIF(Startdate,currentdate,"y") for years only
=DATEDIF(Start...Query to get information that doesn't match by date
I have a table named TTIME with columns in it named: Casenum, Tidate,
I have a table named CLIENTSCASENOTES with columns: Casenum, CNdate, CNnote,
the column TIdate, in the TTIME table stores it's dates as mm/dd/yyyy
the column CNdate, in the CLIENTSCASENOTES tables stores its dates as
mm/dd/yyyy with the time on the end.
What I am trying to do is link the TTIME table with the CLIENTSCASENOTES
table and then get the following out of the query: All the records from
TTIME that do not have a record in the CLIENTSCASENOTES table with date
matching the da...Changing base date?
I was copying data from an opened CSV into a template and noticed that
the all of the dates were wrong!
I changed the numeric format to general in both windows to see if the
base number was the same. They were
I then changed the number to 0 then formatted it as a date and saw one
spreadsheet format the zero as "1/0/1900" and the other spreadsheet
formatted the zero as "1/1/1904"
One computer is using the 1904 date system under tools>options>calculations
"Aaron Bratcher" <firstname.lastname@example.org> wrote i...US-UK date formats & downloading transactions
I have accounts in both the US (=base currency) + UK. I typically download
transactions manually from the institutions I hold accounts with, but the
US-UK date format difference is causing problems (eg. 01/10/05: in the US =
Jan 10th, in the UK = Oct 1st). I can't find a way of dealing with this.
In microsoft.public.money, "Anthro girl" <Anthro
>I have accounts in both the US (=base currency) + UK. I typically download
>transactions manually from the institutions I hold accounts with, but the
>US-UK date form...Conditional Formats
I want to set a set of criteria for a row, within a give date range, in a way
that as the 'due' date approaches, the cells change color from green to
yellow to red. how can i set this up?
You use conditional formatting.
Depending on how you calculate your 'due' date, you use a condition like:
"lau" <email@example.com> wrote in message
>I want to set a set of criteria for a row, within a give date range, in a
> that as the &...