Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
With your relative addressing everything is relative to the active cell, try
changing - ).Address(False, False) to ).Address Regards, Peter T "Keith Wilby" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
"Peter T" <peter_t@discussions wrote in message
... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
"Peter T" <peter_t@discussions wrote in message
... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
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" wrote in message ... "Peter T" <peter_t@discussions wrote in message ... 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Formatting Strangeness
"Peter T" <peter_t@discussions wrote in message
... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting strangeness | Excel Worksheet Functions | |||
Conditional formatting strangeness | Excel Discussion (Misc queries) | |||
Conditional formatting strangeness | Excel Discussion (Misc queries) | |||
ByRef strangeness | Excel Programming | |||
More HpageBreaks Strangeness | Excel Programming |