compare & delete & color ...
Dear all , i have a vba execl question here. I have a two sheets
worksheet ( HPTB and Statement ). HPTB is my trial balance which has 9
columns ( A - I ) , column c is invoice number and column H is amount ;
Statement is vendor's trail balance which has 7 columns ( A - G ) ,
column B is invoice number and column F is amount.
What I try to do is compare these two columns , if they are same
invoice numbers i want to calculate the difference ( use Statement
amount - HPTB amount), and if the difference is Nil , i want to delete
these two rows ; if the difference not Nil , I want to leave this...Error bar
Do anyone know how to edit the width of error bar freely out of the
three options available ?
If you mean the distance from the point to the end of the error bar, you
can assign values from the worksheet, using the Custom (plus) and Custom
(minus) entry boxes.
If you mean the size of the dash at the end of the error bar, Excel
picks it, and all you can do is show it or hide it (Format - Patterns).
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
> Do anyone know how to edi...Creating accessible color-independent charts in Excel 2007
I can't seem to figure out (it was easy in earlier versions!) how to
take pie charts (or other types of charts) and make them independent
of color. I don't mind if the chart uses color, but I don't want to
rely on color to make sure that people of varying sensitivities to
color or a black and white photocopy doesn't put the user of the chart
at a disadvantage. How can I have my different pie slices not just a
different color, but in a different cross-hatch or other fill? Thanks,
and sorry if this is obvious!
They removed the UI for pattern fills in 2007.
Fortunately the f...Excel 2002; Macro for a light color band at the active row
I find it difficult to read the cell content correctly at a distant column
of the same row.
For example to read the cell content of PQ 354 when the active cell is at
B354 when a large worksheet is open in a split window.
A B C D PQ PP
353 xxx T29
354 xxx K26
355 xxx U87
Is there any macro code for a light color band (light green or light yellow)
to turn on when I need it to serve as a "ruler" across an acti...How to make Three Bars and one line with a second axis in EXCEL
I want to make a Chart in EXCEL such that I canhave three or more BARS and
one Line..The line I want in a second axis.
Let's say you have three series that you want for bars and one that you want
plotted as a line. Create a bar chart using all 4 series. Select the
series that you want to change to a line and right click on the series.
You can change the chart type for the series at this point. I believe it
will put it on a secondary axis automatically.
> I want to make a Chart in EXCEL such that I canhave three or more BARS and
> one Line..T...How to change the "Save As" default path
When I open a preexisting file in Excel and choose to save it under a new
name, "Save As", the default path displayed is "C:\My Documents". How can
I set it so that the path displayed is the last one accessed?
Go to Tools|Options
Choose the General Tab
Change the file path for the default file location
You'll need to know the whole path ahead of time because you have to type it out yourself
If you have folder set to display the whole file path in the address bar, I'd suggest navigating to that folder in Explorer, then copying the whole path
You can set folders ...How can I change Format Axis Scale Units of a chart ?
I have several charts which I created. when I view the Scale information for
the X-Axis which always are chronological dates I notice that the "Number of
catagories between tick mark lables" section is just a straight Integer value
for some of my charts and for some of my other charts it is using date values
for the "number of catagories between tick mark labels" how can I change the
charts that are using Date values to use integer values the same as my other
Not sure if this will fix your problem but try...
In 2003 (or earlier) Select the chart, ...Todays DAY?
Hi this is one of those stupid questions!!! :)
Is it possible to return todays DAY? I can get the current time and
date just not the day? Any help would be great.
phade2blue's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24852
View this thread: http://www.excelforum.com/showthread.php?threadid=384620
Vaya con Dios,
"phade2blue" <email@example.com> w...Just a simple form change
I made a very simple form page:
In Settings -> System Customization -> Cases, I selected
teh "Customize Form" link to make changes to the "out of
the box" forms that ship with CRM. For this first
attempt, I simply moved the "Case Type" and "Priority"
fields from the "Details" tab up to the first "General"
tab. With this change, my users can enter all the
crucial details on the first screen.
When I click Preview -> Create Form ... my changes look
great. But when I make new CRM tickets, my changes
aren't there....Numbering Trading days per month from a list of Dates
I have a list of days that the stock market was open. (all of the
closed days have been excluded from the list).
I want to identify what number trading day, within the month, each
Date Trading Day
Is there a combination of functions that will recognize the month
change and identify the trading days until the next month change?
Say the data starts in A2. In B2 enter 1
In B3 enter:
=IF(MONTH(A3)=MONTH(A2),B2+1,1) an...Autoedit case change "i"
I have several applications which use the low case letter "i" as a code
rather than a pronoun. Sometimes but not always Excel changes the lower case
"i' to a uppercase "I" . How do I turn off this feature?
Tools > Autocorrect Options
Pan down the list to i and delete it
Norman Harker MVP (Excel)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"josh" <firstname.lastname@example.org> wrote in message
news:%23dE5kDH%23DHA.2368@...Change page margin size for comment ballons
I want to see two documents side-by-side (not using the native compare-
side-by-side feature, but just putting the windows side-by-side).
Both documents are to be viewed in Print Layout View (full 8x11 inch
page, no fluid wrapping of the text according to window width). I
also want to view the comment balloons on both documents. Needless to
say, this is a tight squeeze, even the wide screens. I would like to
squeeze down the width...is there a way to change the width of the
page margin given to the comment balloons?
I am using Office 2003 on Windows XP.
You can change the "...color column
is there any way to color columns in subform data sheet
let user change color of every column
i can use conditional form but this not let user select prefer color
i hope you understand me
You can modify conditional formatting "on the fly" using code. For example:
Public Function SetColumnBackground(ctl as Control, NewColour as Long)
Dim fc as FormatCondition
If .Count = 0 Then
Set fc = .Add(acExpression,,"1=1")
Set fc = .Item(0)
fc.BackColor = NewColour
I have 10 clients in a protection group, and after the first sync very
little gets pushed daily, about 10MB-50MB on each sync on average. I have 1
client that is pushing over 1Gb on each sync with a total of only 10GBs
pushed on the first sync. I'd like to be able to tell somehow why so much is
getting pushed on each sync as these laptops sometime are on VPN connections
and there is no way that much data could get transferred each day. What
would be the best way to go about figuring this out.
DPM Agent transfers only the files that have got changed(changed bl...Is there any way to recover changes after closing w/o saving?
Well, I did a really stupid thing. Is there any way to recover some recent
changes to a spread sheet after accidentally closing w/o saving? Does excel
happen to make auto recover files one can get to even if the file not damaged?
> Well, I did a really stupid thing. Is there any way to recover some recent
> changes to a spread sheet after accidentally closing w/o saving? Does excel
> happen to make auto recover files one can get to even if the file not damaged?
I have installed a small COM add-in that creates stationary from HTML
templates stored on local computer or network location. I have installed it
on Outlook 2000, XP and 2003 with no problems before. I have one computer
in particular running Outlook XP, where once installed, when I open Outlook,
I get an error telling me comdlg32.ocx could not be found.
I did some research and found it is related to trusted sources and security
in Outlook. If I change his security settings to low, the add-in runs, but
not at the medium level. Computer is a month old running Office XP and
Windows XP Pro.
...Mark my comments with magically changes?
I have one user who recently got a "new" computer, a one-year-old Dell
laptop originally used by someone who is no longer with us. It's
running XP Pro and Outlook 2003 in a Small Business Server 2008 /
Exchange 2007 domain. Server and workstations are all fully patched.
The name in the "Mark my comments with" box spontaneously, apparently
randomly, and frequently changes from hers to mine. I've never had an
account on that system. I may have used OWA on that box, but that's
I don't find any instances of this problem. Anybody got any ideas? I
suppose th...how to change bullet size in MS Word 2007
how to change bullet size in MS Word 2007
"Noam" <Noam@discussions.microsoft.com> wrote in message
> how to change bullet size in MS Word 2007
Home toolbar on ribbon. Click the down pointing arrow on the bullet symbol
in Paragraph section. Click on Define new bullet. Click on Font. Click on
the point size for your new bullet size. Whatever you have typed with a
bullet will change to the new size or format you select.
Hope this helps, let us know.
I'm trying to change the backcolour of all my forms and am using this code
in a module
Public Sub ChangeAllControlProps()
Dim dbCur As Database
Dim doc As Document
On Error GoTo ErrHandler
Set dbCur = CurrentDb
For Each doc In dbCur.Containers("Forms").Documents
DoCmd.OpenForm doc.Name, acDesign
Section.backcolor = RGB(255, 255, 255)
DoCmd.Close acForm, doc.Name, acSaveYes
Set dbCur = Nothing
Select Case Err.Number
Case 438 'Property does not exist
MsgBo...Renaming menu item and changing its prompt text...?
I rename one menu item at runtime as follows...
int nCount = pMenu->GetMenuItemCount();
for (int i = 0; i < nCount; i++)
pMenu->GetMenuString(i, str, MF_BYPOSITION);
if (str.CompareNoCase(_T("Old Name")) == 0)
UINT nId = pMenu->GetMenuItemID(i);
pMenu->ModifyMenu(nId, MF_BYCOMMAND, nId, _T("New
How can I change also its prompt text?
"HS" <email@example.com> wrote in message
...Creating a Simple Bar Chart
I've got 2 columns of numbers - column A is a continuous set of integers
going from about 40 to 210; column B is a frequency count going from blank
up to 10. A sample is shown below.
Using XL2007 I can't even seem to get a simple bar chart showing frequency
count (y axis) against number (x axis). I just get either a total as a
single bar or a cumulative count.
Once I've got this working I'd like to be able to group those numbers (eg
into 5's or 10's) so as to reduce the length of the x-axis
Can SKS help out please.
...Allow to change the refresh interval time of dynamics home page
There should be some way that allows user to change the refreshing interval
time for the dynamics home page.
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.
http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx...Counting Occurences of Hours of the Day
I have a spreadsheet that lists customer contact times by the time of day.
Is there a formula that will count the number of customers each hour, i.e.
00, 01, 02...(24 hour time clock)
Is the timestamp an Excel formatted time, or is it a text string? The
answer to this question drives the way we can figure this out. If
you'll provide an example of your source data it will help out.
It is an Excel formatted time.
"Dave O" wrote:
> Is the timestamp an Excel formatted time, or is it a text string? The
> answer to this question drives the way we...Delay in the First Credit Card Transaction of the day
I have 3 stores and all of them are experiencing the same problem. The first
credit card transaction of the day seems to take a much longer time to
complete/process. Does anyone else have this issue? The delay is about 1
minute but they always go through.
Could this be a problem lying the credit card processor? Is there anyway to
solve this problem? It's just a little awkward that the customer is standing
right in front of the cashier and waiting that long for credit card
transaction to go through.
We have had this same issue at a few locations for sometim...30 Day Rolling Average
I am trying to create a formula to calculate the average of the last
30 days of data I have. I want the average to be based on the today
and average the last 30 days. In addition, I want the formula to
change to take into account what today's date is.
Any help is greatly appreciated.
Assuming the data is in row C, with no embedded blank cells:
If you have fewer than 29 values prior to today, the above will give and erro and requires a
more complicated formula.
On 4 Nov 2003 13:12:43 -0800, firstname.lastname@example.org ...