More Conditional Formatting Woes

[Keith Wilby]

I have coded MS Access to manipulate an Excel Workbook. Some of the code
conditionally formats columns. The code works a treat ... on my PC. When a
colleague runs the same file, the cell range is offset by 10 columns and one
row. I've tried everything I can think of to rectify this but have run out
of ideas. Is there anything I can check in terms of settings, options and
the like?

It's worth noting that I'm starting at column J which is column 10 (the same
as the phantom offset).

We're both running Office 2k3 SP2.

Many thanks.

Keith.

'Format the cells for progress. Red for negative, green for positive, else
white.
Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"), objSht.UsedRange)
'Start from Row 2
With objRange
.Select
.Cells(1, 1).Activate
.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

 

[Joel]

I noticed something unusual with my excel. I selected on a blank workbook
cell A1 and went to Edit Menu - GOTO - SPECIAL - Lastcell. The cell
selected is
H16. Nothing is in H16. I tried to clearcontentts of H16 with no luck. I
do the same thing on sheet 2 and the last cell is A1.

When using your code I found the UsedRange was selecting a larger area than
the actual cells that had data. No sure why. Here is tthe code I used (added
three lines). Step through code and check if objsht.UsedRange.Select is
selecting the same area on the two PCs.

Sub test1()
Set objsht = Sheets("Sheet1")
objsht.UsedRange.Select
Set objXL = ActiveWorkbook.Application
Set objRange = objXL.Intersect(objsht.Range("K2:IV65536"), objsht.UsedRange)
'Start from Row 2
With objRange
.Select
.Cells(1, 1).Activate
.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

End Sub

 

[Keith Wilby]

I noticed something unusual with my excel. I selected on a blank workbook
cell A1 and went to Edit Menu - GOTO - SPECIAL - Lastcell. The cell
selected is
H16. Nothing is in H16. I tried to clearcontentts of H16 with no luck.
I
do the same thing on sheet 2 and the last cell is A1.

When using your code I found the UsedRange was selecting a larger area
than
the actual cells that had data. No sure why. Here is tthe code I used
(added
three lines). Step through code and check if objsht.UsedRange.Select is
selecting the same area on the two PCs.

Hi Joel, many thanks for responding.

I added this line

objsht.UsedRange.Select

after

Set objRange = objXL.Intersect(objSht.Range("K2:IV65536"), objSht.UsedRange)
'Start from Row 2

and it works fine on the other PC now. If only I knew why ;-)

Thanks again.

Regards,
Keith.