I'm working from an Access database to conditionally format cells in Excel.
Here's a snippet of my code. I don't pretend to understand how some of it
works, it was kindly supplied by Bernie Deitrick.

Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"),
objSht.UsedRange) 'Start from Row 2
With objRange
.Select
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3 'Conditionally
format cells red
.FormatConditions.Add Type:=xlCellValue,
Operator:=xlGreater, Formula1:="=" & .Cells(1, 0).Address(False, False)
.FormatConditions(2).Interior.ColorIndex = 4 'Conditionally
format cells red
End With

When I manually select cell K2 in Excel and look up the conditional
formatting it references cell T2 instead of cell J2. That just happens to
be an offset of 10. I'm *sure* this code used to work but I can't for the
life of me work out why it's behaving this way. Any clues anyone?

Many thanks.

Keith.

With your relative addressing everything is relative to the active cell, try
changing -
).Address(False, False)
to
).Address

Regards,
Peter T

"Keith Wilby" <(E-Mail Removed)> wrote in message
news:46a4c2f5$(E-Mail Removed)...
> I'm working from an Access database to conditionally format cells in
Excel.
> Here's a snippet of my code. I don't pretend to understand how some of it
> works, it was kindly supplied by Bernie Deitrick.
>
> Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"),
> objSht.UsedRange) 'Start from Row 2
> With objRange
> .Select
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess,
> Formula1:="=" & .Cells(1, 0).Address(False, False)
> .FormatConditions(1).Interior.ColorIndex = 3
'Conditionally
> format cells red
> .FormatConditions.Add Type:=xlCellValue,
> Operator:=xlGreater, Formula1:="=" & .Cells(1, 0).Address(False, False)
> .FormatConditions(2).Interior.ColorIndex = 4
'Conditionally
> format cells red
> End With
>
> When I manually select cell K2 in Excel and look up the conditional
> formatting it references cell T2 instead of cell J2. That just happens to
> be an offset of 10. I'm *sure* this code used to work but I can't for the
> life of me work out why it's behaving this way. Any clues anyone?
>
> Many thanks.
>
> Keith.
>

"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> With your relative addressing everything is relative to the active cell,
> try
> changing -
> ).Address(False, False)
> to
> ).Address
>

Hi Peter, I neglected to mention what the conditional formatting was but
you seem to have sussed it. The cell referencing seems to be OK with your
suggested mod, what do the "False" arguments actually do?

Thanks.

Keith.

"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> With your relative addressing everything is relative to the active cell,
> try
> changing -
> ).Address(False, False)
> to
> ).Address
>

Hi Peter, I spoke too soon as all of the cells in the range now reference
row 2.

Actually your original code seems fine, providing the active (selected) cell
is K2, thereafter everything is relative. In your OP you say when you select
K2 the CF formula refers to T2, that would imply when the CF was applied A2
was active which seems odd looking at your code.

in your adjacent post
>what do the "False" arguments actually do?

They return relative address without the $'s

Msgbox activecell.address & vbcr & activecell.address(false,false)

Regards,
Peter T

"Keith Wilby" <(E-Mail Removed)> wrote in message
news:46a4c9df$(E-Mail Removed)...
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > With your relative addressing everything is relative to the active cell,
> > try
> > changing -
> > ).Address(False, False)
> > to
> > ).Address
> >
>
> Hi Peter, I spoke too soon as all of the cells in the range now reference
> row 2.
>

"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> Actually your original code seems fine, providing the active (selected)
> cell
> is K2, thereafter everything is relative. In your OP you say when you
> select
> K2 the CF formula refers to T2, that would imply when the CF was applied
> A2
> was active which seems odd looking at your code.
>

Hi again Peter, thanks for your patience. At runtime, in the immediate
window, when I paste

?objXL.Intersect(objSht.Range("K2:IV65536"), objSht.UsedRange)

I get a "Type mismatch" error. Any ideas why that might be?

Regards,
Keith.

"Peter T" <peter_t@discussions> wrote in message
news:%(E-Mail Removed)...
> Actually your original code seems fine, providing the active (selected)
> cell
> is K2, thereafter everything is relative. In your OP you say when you
> select
> K2 the CF formula refers to T2, that would imply when the CF was applied
> A2
> was active which seems odd looking at your code.
>

Hi Peter, please ignore my earlier post today. It would seem that my Access
VBA project had become corrupted because running a /decompile and then
re-compiling seems to have rectified the problem.

Thanks for your time.

Regards,
Keith.

"Keith Wilby" <(E-Mail Removed)> wrote in message
news:46a5ab5b$(E-Mail Removed)...
> "Peter T" <peter_t@discussions> wrote in message
> news:%(E-Mail Removed)...
> > Actually your original code seems fine, providing the active (selected)
> > cell
> > is K2, thereafter everything is relative. In your OP you say when you
> > select
> > K2 the CF formula refers to T2, that would imply when the CF was applied
> > A2
> > was active which seems odd looking at your code.
> >
>
> Hi Peter, please ignore my earlier post today. It would seem that my
Access
> VBA project had become corrupted because running a /decompile and then
> re-compiling seems to have rectified the problem.
>
> Thanks for your time.
>
> Regards,
> Keith.

Glad you got it working with the recompile, but just wondering if that might
be a coincidence. Looking at your original code again -
Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"),objSht.UsedRange)

If the no part of the Usedrange exists within "K2:IV65536" objRange will
remain as 'Nothing' causing the rest of your code to work incorrectly.

Regards,
Peter T