My first post here. I have made a lot of VBA code, but without the basic competence of a programmer (being a process engineer) I always tend to paint myself into a corner...
Now here is the deal:
I made some tedious code to construct a FormulaRange (typically=$D$6:$E$357;$H$6:$I$357;$L$6:$L$357;bla bla bla;$DX$6:$DX$357) containing all cells that should have formulas in them. I want all cells that are manually overwritten to be flagged RED.
The IsFormula function is stolen from you guys, ref. Excel Conditional Format for cells containg a formula
The formula looks fine and appears like this: =NOT(IsFormula(D6))
The problem occurred on my office computer with Win XP and Excel 2007: The macro just halts after the formula definition. No error (which also happens to occur when I have a macro insert a row into this spreadsheet when it has conditional formatting rules).
So this leaves me with a perfectly good conditional formatting rule but no formatting set for it. And obviously this is also the last action my macro will perform, which was not my intention.
However, last night I emailed this workbook to my home computer to continue debugging. And: on Win 7 64-bit, Excel 2010 everything works!
Being a lousy programmer, I always cheat my way around parameter declarations etc. It usually works anyway.
Anyone?
Now here is the deal:
I made some tedious code to construct a FormulaRange (typically=$D$6:$E$357;$H$6:$I$357;$L$6:$L$357;bla bla bla;$DX$6:$DX$357) containing all cells that should have formulas in them. I want all cells that are manually overwritten to be flagged RED.
Code:
With FormulaRange
.Select
.FormatConditions.Add Type:=xlExpression, Formula1:="=NOT(IsFormula(" & ActiveCell.Address(False, False) & "))"
.FormatConditions(1).Font.ColorIndex = 3 '1=black,3=red,5=blue,xlAutomatic
End With
Code:
Function IsFormula(c)
IsFormula = c.HasFormula
End FunctionThe problem occurred on my office computer with Win XP and Excel 2007: The macro just halts after the formula definition. No error (which also happens to occur when I have a macro insert a row into this spreadsheet when it has conditional formatting rules).
So this leaves me with a perfectly good conditional formatting rule but no formatting set for it. And obviously this is also the last action my macro will perform, which was not my intention.
However, last night I emailed this workbook to my home computer to continue debugging. And: on Win 7 64-bit, Excel 2010 everything works!
Being a lousy programmer, I always cheat my way around parameter declarations etc. It usually works anyway.
Anyone?
