Range conditional formatting in Excel from Access VBA
 

Hi !

Using Access XP, I'm generating an Excel worksheet.

I have two questions :

1) If I use the following line to select two non contiguous ranges

With xlsSheet.Range("H3:P36,R3:Y36")
...

I get the error message 1004 : method 'Range' of '_Worksheet' failed.

This is strange because the macro recorder uses the same syntax :
Range("H3:P36,R3:Y36").Select

(I tried the .Select method in Access VBA as well, with the same
result).

What wrong ?


2) Is it possible to apply a conditional formatting to all cells of a
range without :
- having to loop thru each cell ?
- use .FormatConditions, because I have more than 4 conditions
(actually 6)

The formatting I want to set is the bacgroung color only.

Since the file I generate is static (ie: won't be edited later), I
don't need the formatting logic to be dynamic.


Thanks !

FU2: microsoft.public.access

Range conditional formatting in Excel from Access VBA
 

Thanks for your answer.. but allow me to add :

#1 : Agreed with the activesheet (which it is in my case)..

My question is more : in my Access VBA program, why do
xlsSheet.Range("H3:P36") works correctly but
xlsSheet.Range("H3:P36,R3:Y36") don't when both forms work in Excel ?


#2 : I know .FormatCondition is limited to only 3 conditions.. That's
why I told I was looking for a solotion avoiding the ".FormatContition"
property.

I was more thinking of something like the "SUMIF" function (FORMATIF
??) or a Matrix (Array) formula applied to the whole range.

Thanks !


Dave Peterson avait énoncé :
#1. You can only select a range on the activesheet in the activeworkbook.

xlssheet.parent.activate
xlssheet.select
xlssheet.range("...").select

But there aren't that many things in excel that need to be selected to work
on. You may just want to work on the range directly:

xlssheet.range("...").clearcontents '???????

#2. You only get 3 conditions in xl97 to xl2003.



"Michel S." wrote:

Hi !

Using Access XP, I'm generating an Excel worksheet.

I have two questions :

1) If I use the following line to select two non contiguous ranges

With xlsSheet.Range("H3:P36,R3:Y36")
...

I get the error message 1004 : method 'Range' of '_Worksheet' failed.

This is strange because the macro recorder uses the same syntax :
Range("H3:P36,R3:Y36").Select

(I tried the .Select method in Access VBA as well, with the same
result).

What wrong ?

2) Is it possible to apply a conditional formatting to all cells of a
range without :
- having to loop thru each cell ?
- use .FormatConditions, because I have more than 4 conditions
(actually 6)

The formatting I want to set is the bacgroung color only.

Since the file I generate is static (ie: won't be edited later), I
don't need the formatting logic to be dynamic.

Thanks !

FU2: microsoft.public.access