I have a sheet that I want to do some conditional formatting on from a
macro... So I started recording, selected the column I was intereted in
("A:A") and did the conditional format. Everything worked great.

When I put the appropriate code in the macro and run it, however, things go
screwey. The line where I select the column using 'Columns("A:A").Select'
actually selects columns A thru F because there is a cell that is merged
over the A-F columns.

Q #1: How can I tell the .Select function to NOT include the columns over
which the merged cells run?

Q #2: I figured I would just be able to look up the 'Select' method in the
Excel VBA reference at msdn, thinking there might be some parameters I
could pass. But strangely, they don't list the 'Select' method at all!
Why not?? Is there any reference documentation for this function?? I'm
sure they didn't just forget about it - I must be missing something.

Thanks for any help!

-mdb

Normally you want to avoid the select method as there is rearely any need to
actually select. Conditional formatting however is one of those very rare
circumstances that requires a select. The Select method has no parameters. it
take a range object and selects the cells in that range. There is no way to
avoid selecting cetain cells in that range.

Merged Cells are a problem. Most XL experts avoid them. Code and merged
cells con't get along. Instead of a merged cell have you tried formatting the
text to center horizontally across the selection? Remove the Merge and then
reformat the cells as follows.

Format Cells... - Alignement tab - Horizontal: - Center Across Selection

The formatting is almost identical except that it does not have the same
code issues as merged cells...
--
HTH...

Jim Thomlinson


"Michael Bray" wrote:

I have a sheet that I want to do some conditional formatting on from a
macro... So I started recording, selected the column I was intereted in
("A:A") and did the conditional format. Everything worked great.

When I put the appropriate code in the macro and run it, however, things go
screwey. The line where I select the column using 'Columns("A:A").Select'
actually selects columns A thru F because there is a cell that is merged
over the A-F columns.

Q #1: How can I tell the .Select function to NOT include the columns over
which the merged cells run?

Q #2: I figured I would just be able to look up the 'Select' method in the
Excel VBA reference at msdn, thinking there might be some parameters I
could pass. But strangely, they don't list the 'Select' method at all!
Why not?? Is there any reference documentation for this function?? I'm
sure they didn't just forget about it - I must be missing something.

Thanks for any help!

-mdb

#1. I don't think you can specify this.

#2. Maybe you can look at VBA's help.

#3. It seems each version of excel treats merged cells differently. You'll
want to specify the version of excel that you're using to get responses for that
version.

#4. Merged cells are painful to work with. I try my best not to use them.

#5. Most things you do in excel don't require that you select them to work on
them. Maybe you can change your code to work directly on the range.

Michael Bray wrote:

I have a sheet that I want to do some conditional formatting on from a
macro... So I started recording, selected the column I was intereted in
("A:A") and did the conditional format. Everything worked great.

When I put the appropriate code in the macro and run it, however, things go
screwey. The line where I select the column using 'Columns("A:A").Select'
actually selects columns A thru F because there is a cell that is merged
over the A-F columns.

Q #1: How can I tell the .Select function to NOT include the columns over
which the merged cells run?

Q #2: I figured I would just be able to look up the 'Select' method in the
Excel VBA reference at msdn, thinking there might be some parameters I
could pass. But strangely, they don't list the 'Select' method at all!
Why not?? Is there any reference documentation for this function?? I'm
sure they didn't just forget about it - I must be missing something.

Thanks for any help!

-mdb

--

Dave Peterson

?B?SmltIFRob21saW5zb24=?=
wrote in
:

Format Cells... - Alignement tab - Horizontal: - Center Across
Selection


That worked like a charm!! Thanks so much!

-mdb

Except that the requirement is for conditional formatting...

http://support.microsoft.com/kb/895562

Unless the conditional formats are based on absolute references you need to
select the range... Unless you have some work around...
--
HTH...

Jim Thomlinson


"Dave Peterson" wrote:

#1. I don't think you can specify this.

#2. Maybe you can look at VBA's help.

#3. It seems each version of excel treats merged cells differently. You'll
want to specify the version of excel that you're using to get responses for that
version.

#4. Merged cells are painful to work with. I try my best not to use them.

#5. Most things you do in excel don't require that you select them to work on
them. Maybe you can change your code to work directly on the range.

Michael Bray wrote:

I have a sheet that I want to do some conditional formatting on from a
macro... So I started recording, selected the column I was intereted in
("A:A") and did the conditional format. Everything worked great.

When I put the appropriate code in the macro and run it, however, things go
screwey. The line where I select the column using 'Columns("A:A").Select'
actually selects columns A thru F because there is a cell that is merged
over the A-F columns.

Q #1: How can I tell the .Select function to NOT include the columns over
which the merged cells run?

Q #2: I figured I would just be able to look up the 'Select' method in the
Excel VBA reference at msdn, thinking there might be some parameters I
could pass. But strangely, they don't list the 'Select' method at all!
Why not?? Is there any reference documentation for this function?? I'm
sure they didn't just forget about it - I must be missing something.

Thanks for any help!

-mdb

--

Dave Peterson

I missed the portion about conditional formatting.

I haven't looked at this in a while, but maybe this would work:

http://spreadsheetpage.com/index.php..._form atting/

(from John Walkenbach's site)

Oops. I guess that's the same workaround as in the KB article.

Jim Thomlinson wrote:

Except that the requirement is for conditional formatting...

http://support.microsoft.com/kb/895562

Unless the conditional formats are based on absolute references you need to
select the range... Unless you have some work around...
--
HTH...

Jim Thomlinson

"Dave Peterson" wrote:

#1. I don't think you can specify this.

#2. Maybe you can look at VBA's help.

#3. It seems each version of excel treats merged cells differently. You'll
want to specify the version of excel that you're using to get responses for that
version.

#4. Merged cells are painful to work with. I try my best not to use them.

#5. Most things you do in excel don't require that you select them to work on
them. Maybe you can change your code to work directly on the range.

Michael Bray wrote:

I have a sheet that I want to do some conditional formatting on from a
macro... So I started recording, selected the column I was intereted in
("A:A") and did the conditional format. Everything worked great.

When I put the appropriate code in the macro and run it, however, things go
screwey. The line where I select the column using 'Columns("A:A").Select'
actually selects columns A thru F because there is a cell that is merged
over the A-F columns.

Q #1: How can I tell the .Select function to NOT include the columns over
which the merged cells run?

Q #2: I figured I would just be able to look up the 'Select' method in the
Excel VBA reference at msdn, thinking there might be some parameters I
could pass. But strangely, they don't list the 'Select' method at all!
Why not?? Is there any reference documentation for this function?? I'm
sure they didn't just forget about it - I must be missing something.

Thanks for any help!

-mdb

--

Dave Peterson


--

Dave Peterson