milesUK
Active Member
- Joined
- Jan 5, 2003
- Messages
- 388
Good morning (it was when I bagan this post ),
I have an Excel macro which includes the following snippet of code to apply conditional formatting to a cell populated with a formula also applied by the macro
An ODBC link then refeshes the cells to the left of column M and is set to copy down the formulas (and formatting) in adjacent cells.
Ocasionally when the macro is run I get the correct cell reference in the conditional formatting of cell M3 (i.e. =K3) but usually I get references either to a few rows down (eg =$K4, or =$K7) or more often in the region of =K65527 The cells below M3 increment until 65536 (max row number) is reached then continue with references from 1 onwards. This problem is present before (as well as after) the ODBC refresh edit-(ie M3 has a conditional format applied but referencing the wrong cell).
I also used to get strange column references as well but cured that one by the inclusion of the absolute reference ($) for the column; can not do same for row ref as it will not copy down correctly.
Has anyone any ideas about this please? It's getting very frustrating and I guess I'm just missing something simple. edit-(Not so simple eh?)
I have an Excel macro which includes the following snippet of code to apply conditional formatting to a cell populated with a formula also applied by the macro
Code:
With Range("$M3")
.FormatConditions.Delete
.FormatConditions.Add xlCellValue, xlEqual, "=$K3"
.FormatConditions(1).Interior.ColorIndex = 7 'magenta
End With
Ocasionally when the macro is run I get the correct cell reference in the conditional formatting of cell M3 (i.e. =K3) but usually I get references either to a few rows down (eg =$K4, or =$K7) or more often in the region of =K65527 The cells below M3 increment until 65536 (max row number) is reached then continue with references from 1 onwards. This problem is present before (as well as after) the ODBC refresh edit-(ie M3 has a conditional format applied but referencing the wrong cell).
I also used to get strange column references as well but cured that one by the inclusion of the absolute reference ($) for the column; can not do same for row ref as it will not copy down correctly.
Has anyone any ideas about this please? It's getting very frustrating and I guess I'm just missing something simple. edit-(Not so simple eh?)