Formatting Excel Range

[sjh]

Hi,
I get an error message with the following code. This code is executed in another sheet, not in "mySheet". What am I doing wrong??? I would like to select a range in "mySheet" and make it bold.

Sheets("mySheet&quot.Range(Cells(9, 4), Cells(9, 4)).Font.Bold = True

Run-time error '1004':
Application-defined or object-defined error


Thank you!

 

[TopJack]

Use this statement before your line ......

Sheets("mySheet&quot.Activate

You need to make your sheet current before you can update. Hope it helps.

 

[DaleWatson123321]

Try this...

Sheets("mySheet&quot.Cells(9, 4).Font.Bold = True

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

sjh,

My curiosity is getting the better of me... I need to ask: In your VBA code, do you normally reference cells by using numbers and/or by using cell coordinates ???

If so, I believe you'll find the following to be very useful information... (In another thread, a member commented that this information should be "required reading" for all VBA programmers.)

First, let me give you an example of how your code could (and really should) be written...

[total].Font.Bold = True

In the above example, "total" refers to a cell that has been assigned a "range name".

I feel that whether anyone is either a "seasoned" VBA programmer, or "new", it's IMPORTANT to STRONGLY RECOMMEND the use of "Range Names".

I also appreciate that some who post examples with cell coordinates are actually using range names, but to "simplify" matters, are just using the cell coordinates in the example.

Nonetheless, I know there are MANY Excel users who still don't appreciate the EXTREME VALUE of using Range Names, so "here goes my salvo" on the reasoning for using Range Names...

In writing code that uses cell coordinates, it REQUIRES that EACH and EVERY time a change is made to the worksheet(s), the code must be changed. Changes include for example: insertion or deletion of row(s) or column(s), or moving data from one place to another.

With many applications, they "start out small" but then "evolve" into larger and larger applications - with MANY changes. With the use of range names, many (if not most) of the changes will NOT require changes to the VBA code - and can be a HUGE time-saver.

The "key" to appreciating the use of range names, is that "internally", Excel maintains a "link" between the names and the cell coordinates to which they are assigned. So whenever changes are made to the workbook, the range names are automatically adjusted. And by having VBA code that references range names, there is usually nothing to change.

Generally, range names can also very useful in identifying various blocks of data, or different locations throughout the workbook. These named ranges can then easily be referenced with VBA for formatting, printing, or referencing in formulas, etc. It can also be appropriate to create range names that the end-user can go to - by hitting the &quot;GoTo&quot; key <F5>, followed by the name - e.g. &quot;REVENUE&quot;, or simply: R (either UPPER or lower case work and is &quot;faster&quot.

Years ago it seemed commonplace for &quot;hard-coding&quot; to be considered a &quot;No-No&quot;. Using cell-coordinates is, to me, &quot;hard-coding&quot;.

I hope my &quot;sermon&quot; will be appreciated, and that those who have not yet begun to create and use Range Names will &quot;give it a try&quot;. I'm confident you'll be pleased with the results.

While there are different ways to create Range Names, the method I recommend is:
a) Highlight the cell or range-of-cells
b) Hold down the <Control> key and hit the <F3> key
c) Type the name
d) Hit <Enter>

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca