FormatConditions VBA Bug?

  • Thread starter Thread starter DaveCrowley
  • Start date Start date
D

DaveCrowley

Hi Guys,

I currently have the pleasure of having to get up to speed with some
undocumented spreadsheets. To aid me, I'd like to visually identify all
formulas within these spreadsheets. I have been able to do this manually
successfully using "Conditional Formatting..." and a user defined
function (see below).

This feature worked a treat but having it switched on all the time
appeared to degrade performance. Therefore I tried to create two
macros. One macro to enable "Conditional formatting..." for all cells
in the current sheet, and a second macro to disable it. That way I can
switch it on and off as required. The macro to disable/delete the
"Conditional formatting" works a treat but the macro to enable it does
not. Stepping through the code (see below), it executes the first three
statements in the macro, then exits the routine.

Am I doing something silly or have I unearthed a bug? Any advise/help
would be highly appreciated.

Cheers, Dave.


Function IsFormula(Check_Cell As Range)

IsFormula = Check_Cell.HasFormula

End Function

Sub Show_Formulas_in_Current_Sheet()

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IsFormula(A1)"
With Selection.FormatConditions(1).Borders(xlLeft)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlRight)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With

End Sub
 
Hi Dave,

could it happen that selection already contains conditionally formatted
cells?

then:

Sub Show_Formulas_in_Current_Sheet()

Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.FormatConditions.Delete 'added
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=IsFormula(A1)"
With Selection.FormatConditions(1).Borders(xlLeft)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlRight)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlTop)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With
With Selection.FormatConditions(1).Borders(xlBottom)
..LineStyle = xlContinuous
..Weight = xlThin
..ColorIndex = 3
End With

End Sub

Regards,
Ivan
 
this may be easier

Sub borderaround()
Range("A1", Range("a1").SpecialCells(xlLastCell)) _
..borderaround ColorIndex:=3
End Sub


--
Don Guillett
SalesAid Software
(e-mail address removed)
"DaveCrowley" <[email protected]>
wrote in message
news:D[email protected]...
 
Thanks Ivan.

I tried your suggestion but the problem persists. Are you able to
perhaps recreate the problem? I want to try and eliminate a bad
install/image of Excel as the cause of the problem.

Cheers, Dave.
 
Thanks for the suggest Don. How would I use this code snippet with
"FormatConditions.Add Type"?

Cheers, Dave.
 
Hi Dave,

but your code works.

I don't know what you intended, but when I select a1 (which contains a
formula), the conditional formatting is applied to a1 without any
problems.

I can't recall reliably, I had to apply conditional formatting cell by
cell when I needed to do it programmaticaly. It was not possible to do
it for more cells at once.

Regards,
Ivan
 
Hi Dave,

now I recalled how I solved the problem with conditional formatting. I
exported the module, opened in notepad, added one line:
Attribute Macro1.VB_ProcData.VB_Invoke_Func = " \n14"
just below the sub somename().

Regards,
Ivan
 
sorry, incidentally posted before finished

so, after you add the line, import the module back. You will not see
the line in VBA editor (the more, you will not be able to add it in VBA
editor).

I don't know what exactly the line means, I found it while comparing in
notepad recorded macro and written macro - this was the only difference
(besides that the recorded macro worked and written didn't).

Regards,
Ivan
 
Back
Top