Range conditional formatting in Excel from Access VBA

[Michel S.]

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("H336,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("H336,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

 

[Dave Peterson]

#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.]

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("H336") works correctly but
xlsSheet.Range("H336,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é :

 

[Tom Ogilvy]

is xlsSheet the activesheet. If not, is should be.

Only 3 conditions in conditional formatting


Turn on the macro recorder, select several cells, apply some typical
conditional formatting.

Turn off the macro recorder and look at the code.