How to write a macro to modify an Excel chart
I want to "record" a macro that, for example, changes the line styles on an
Excel chart. I tried by selecting the chart, then clicking on "record
macro", manually changing the line style, and then clicking on "stop
recording". But then when I tried to use the macro it wouldn't do anything.
When I edited the macro there was no code, just the comment lines giving the
Is it possible to use the "record" feature for macros that work with charts,
or must one start with the VB editor?
leave well enough alone
Here ...conditional format (different colors needed)
I have a golf spreadsheet. I want the par scores displayed in
blue and the birdie scores displayed in red.
eg on a par 4 hole any 3's would be in red, any 4's would
be in blue
Message posted from http://www.ExcelForum.com
Assume the set-up below is in cols A to C,
data from row2 down
Hole Par Score
etc (till Hole 18 ?)
Col C (Score) is where you'd apply
the conditional formatting (CF)
Click Format > Conditional Formatting
In the settings tab, make the settings as:
For Condition 1 (For Score = Birdie*):
Formula Is |...Excel behaves differently during macro record.
I have an Excel 2002 worksheet. Calc is auto.
Almost all my cells are calls to a couple of functions I wrote (I
presume they're called UDFs).
On the menu, when I execute:
- Insert / Rows
Excel inserts a blank row. Great. But when I execute:
- Tools / Macro / Record New Macro / <Get new-macro dialog and press
Enter to continue> / Insert / Rows
the blank row inserts, but *bang*, all cells immediately revert to
Now, I've had at least 3 flavors of the problem:
- After everything turns to #NAME, getting the values to revive
requires me to go thru every cell ...In Excel need macro to tell if Text Box is Blank or has Text
In Excel I need a macro to tell me if the Text Box Number is Blank or has
Text in it.
Textboxes on the worksheet?
'if from the control toolbox toolbar
'if from the Drawing toolbar
MsgBox Len(Worksheets("sheet1").TextBoxes("Text box 2").Text)
You could just look to see if it was equal to "", too.
Jerry Dyben wrote:
> In Excel I need a macro to tell me if the Text Box Number is Blank or has
> Text in it.
...Modify macro to extract acronyms
I frequently use the macro to extract acronyms into another document posted
in http://www.thedoctools.com/index.php?show=mt_acronyms_extract. Is there a
way to put the paragraph number in the third column instead of the page
.Cell(n + 1, 1).Range.Text = strAcronym
'Insert page number in column 3
.Cell(n + 1, 3).Range.Text =
Thanks in advance.
Just what do you mean by the paragraph number?
Are the paragraphs numbered by the use of a style that includes the
numbe...macro ...if command, I suppose...
I recorded a macro wich finds the word "new date" and then copies the
corresponding rows in another sheet... (no problem as far as here).
I would like if it found no occurrence it ended e gave a message as "no
occurrence found" instead of indicating a run time error.
I think it need an if command, I tried but without success...
I send the mail
Cells.Find(What:="new date", After:=ActiveCell,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
ActiveCel...Help needed. If conditions and locking cells
I have two columns that I am working with. Column A says "Is Employee?"....
Column B says "If Employee 'yes', enter name"
Is Employee? If Employee yes, enter name.
The "is employee?" column has a drop down menu with the yes and no option. I
need a function where if column A is yes, then user can type name in column B.
HOWEVER, if a cell in column A is NO, then user cannot type anything in
adjacent cell column B. (It would be nice to have a function where if a cell
in column A is No, then cell...Conditional Format
Is there a way to format a cell based on the day of the month?
I'd like the cell colour to change if it's the 24th or 25th of the
Thanks if you know.
Suppose your dates are in column D, starting with D2. Highlight the
cells from D2 down, then bring up the CF dialogue box. Select Formula
Is rather than Cell Value Is in the first box, and enter this formula:
then click on the Format button and choose your background colour from
the Patterns tab. OK your way out.
Hope this helps.
On J...VBA Identifying Active Conditional Formatting
Is there a way to identify if conditional formatting is being applied
to a given field?
I have a form where I'm identifying missing mandatory data by using
conditional formatting to turn the field red. However, I have a
"Reviewed" field that needs to be checked after the data has been
verified by the user, and I want to code a quick check to make sure
none of the mandatory data is missing before allowing them to move on.
The list of mandatory fields is conditionally linked to another field
on the record (ie: if you buy a product, you have to supply a vendor
name; if you make i...Using wildcards with logical (IF, AND) conditional formatting
I have two columns: A & B. Assume A contains a combinaton of numbers and
letters (e.g. 13.00.10i.022) and is formated as "General." Column B is a
whole number between 1 and 3. I would like to create a Conditional Format
formula that says "IF B=1 AND A does NOT begin with the number "12", then
color that ROW red. Continuing, "IF B=1 AND A DOES begin with the number
"12," then do nothing (no color format). How do I do this?
CF>Formula is =AND(B1=1,LEFT(A1,2)<>"12")
The no color part takes care of itse...how: if, conditional test, true (do nothing if false)
I want a simply if then without the else.
will give a blank for a false response,
is this what you mean?
> I want a simply if then without the else.
We have daily and weekly (and monthly and biannual) checks, I want the date
on the weekly to update (dd/mm/yy) on "Mon" but not change the rest of the
> =if(Condtion,true response,"")
> will give a blank for a false response,
> is this what you mean?
> "jwhitney" wrote:
> > I want a...IF function in a macro
I need help with a macro. I have a column of numbers
This column could have ten rows it could have a thousand rows. I need to
change the data into two columns
Col A Col B
I need to strip out any negative number and if the number is negative place
a C in the column next to it. I can do it by using Two IF Statement
In column B I put
In column C I put
This works if I copy and paste it, but how do I conv...Automatically run Macro at specific time
How do I automatically run a macro at a specific time
each day. Ex. run macro at 8:00 each work morning.
And Chip Pearson has some very nice notes at:
You also may want to look at a scheduler program (some versions of windows have
this built in, but there are lots of shareware programs that do it, too
Then that windows scheduler program can start your file at 8:00 and your
workbook could have an auto_open macro that runs when the workbook opens.
> How do I automatically run a macro at a specifi...countif condition with date
I Have A Column A,b, And C
Jan-1 1 1
Jan-2 1 2
Jan-3 1 3
Jan-4 1 1
Jan-5 1 1
Jan-6 1 1
I Want To Count B=1 To C=1&2 And I Dont Want To Count B=1 & C=3
Total Is 5
2nd Problem I Want To Count Base On The Covered Date
I Want To Count From Jan 1 To Jan 4 Only B=1 To C=1&2
Total Is 3
Tnx In Advance
xtrmhyper's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23851
View this thread: http://www.excelforum.com/show...conditional format sum if
i would like to put in a conditional format for cell A3.
in cells A1 and A2 i have dates and i want the formatting
in A3 to come into effect if A2-A1 is greater than 9 days
but i cannot get the right formula.
many thanks if anyone can help.
CF1: Formula Is =(A2-A1)>9
In article <firstname.lastname@example.org>,
"Micayla" <email@example.com> wrote:
> i would like to put in a conditional format for cell A3.
> in cells A1 and A2 i have dates and i want the formatting
> in A3 to come into effect if A2-A1 is greater...Finding a row with the heigest/lowest value under specific conditions.
Goal: Find the row with the highest of lowest value (can be with extra
Example 1 find the row with the highest value.
Example 2 find the row with the highest value not exceeding 3.5.
What is the most elegant (???) way to do this ?
The examples are simple enough, but if they become part of a larger query,
the solutions become less elegant.
(In example 2 the condition is repeated which I consider less elegant.
In the second solution for example 2, the with is used this only works in
recent versions of SQL-server and not in other SQL implementations)
S...How do I get a macro to be non worksheet specific?
I can't figure out how to make a macro non worksheet specific like they were
in previous versions of Excel. I want to have a macro that will sort the
same range of cells of whatever sheet that I am in, rather than having the
same macro for each sheet with a different name.
In EXCEL 2007:-
When you record your Macro for the first time you will get the Macro panel
up (where you name it).
In here there is a section called:-
Store macro in:
It is here that you can select one of the options to make the macro
available outside of that Workbook only.
Please hit yes ...Macro to match 2 column values in different sheets
I need a macro which compares the values in colA and colC of sheet 1
to column D and column E of sheet 2, if both the value match, then it
copies the contents from sheet2 col F,G and H into sheet1 colC,D and
I tried the code below but I got an error message saying:Subscript out
Please advise if any modifications required in the following code:
For iRow = 1 to 1000
If Sheets("Sheet1").cells(iRow, 1).Value = Sheets("Sheet2").cells
(iRow, 4).Value And _
Sheets("Sheet1").cells(iRow, 3).Value = She...macro to close document if macros disabled
I have several users forms that have code to protect the form and alter
behavior of enter key. When opening security settings request user to enable
or disable macros. Sometimes new users disables macros this becomes
problematic since the macros are in place to provide form protection and
Is there code that would force the document to close if macros are disabled?
Any advice appreciated.
No, there is not (and could not be) such code, because it could never run if
macros are disabled.
The best you can do is to completely disable any editing of the document
Is there a way to get a cell's format determined by the format of
a different cell?!
e.g. Could one get an entire row market up in say bold red if
one cell in that row was say less than 100
select the rows that you want to apply the conditional formatting to,
ensuring that the first row of this group is the first row at the top of the
screen (it gets confused sometimes if it isn't)
the following example assums you've selected from row 2 onwards and you want
the row to turn red if ...Checking if files open prior to running macro
Is it possible to check if a number of files are open
prior to running a macro that pulls data from the required
After testing with a file open, Excel does not prompt you
that the files is already open but when trying to save the
files to overwrite the original the macro falls down.
Thanks in advance!!!
This is a repost--newsreader problems. But I did want to add that if you're
using xl2k or higher, you can remove the split97 function and just change this
mySplit = Split97(FileNameToCheck, "\")
mySplit = Split(FileNameToCheck, "\")
(Split was a...Hiding Column based on If Condition
I would like to know if it is possible to hide Column/Row based on I
condition. (If the cell A1 has a particular value then hide ColumnB).
Incase this isn't possible, can you help me with this problem -
I'm having a drop down list with Jan-Dec in Cell A1. In Cells B2:G10,
need to input a value corresponding to each of the months. This valu
I'm retreiving and using in a different location using If Condition
The problem is that incase I change the month to Feb, the value
inputted for Jan remains the same throughout. Any change made for Fe
gets reflected throughout. I'm...BLANK cell as a result of IF condition
I'm trying to find a way how to use IF condition:
=IF(MyCondition; ValueIfTrue; ... else leave cell empty ...).
=IF(MyCondition; ValueIfTrue; "") is not good for me, because it does not
leave the cell empty.
Thanks in advance for every suggestions.
You can't. A formula must return a result, even if only an empty string as
you have, so if you have a formula in a cell, the cell will not be empty.
"Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message
news:57EFEB43-F266-424E-BD2A-E19F44D189C3@micros...Using a macro to paste a row that contains a specific value
I've another macro(or anyway this could be accomplished) query.
What I have is another trucklog (so far they're pleased with the
other, thanks to you people :D) That has multiple Identicle sheets.
Now the main sheet has a column (col B to be percise) named "Haul #".
What I need is to have it copy rows from the Main Sheet into the
identicle ones based on the data in the "Haul #".
So basically the rows that have 365 in the "Haul #" column go to one
sheet. The rows with 355 in the "haul #" goes into yet another... so on
and so forth.
How on ear...count occurances of values in column if condition is met
From a cell I need to look down column B and for each time there is
the value =93Ship Effort=94 I need to look in Column E and count the
occurrences of items in range =93SysListName=94 .
For example if SysListName
And Column B =3D
And Column E =3D
The formula would return =932=94
and help will be appreciated