condition formatting based on presence/absence of formula in cell
Posted by coline on January 28, 2002 10:39 PM
Is there a simple way to specify a cell format if a formula is present in the cell?
Thanks
Posted by Tom Urtis on January 28, 2002 11:42 PM
Here's one way to do it
Hello Coline,
For example, if you want to highlight cells in the range A1:C10 that contain formulas, right-click on your sheet tab, left click on View Code, and paste this in.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MonitorRange As Range
Dim Cell As Range
Set MonitorRange = Range("A1:C10")
For Each Cell In MonitorRange
If Cell.HasFormula Then
Cell.Interior.ColorIndex = 6
Else
Cell.Interior.ColorIndex = 0
End If
Next
Set MonitorRange = Nothing
End Sub
Modify the color index and range to suit your needs.
One advisement: resist the temptation to set your monitor range to A1:IV65536, in case you want to "cover your bases" in monitoring the entire sheet. Your system will not appreciate looping through almost 17 million cells. Set a realistic range that formulas will reasonably rest within. If you think about it, this sort of code is most practical with sporadic instances of formulas within a small range. So, if you have a large range that you know will contain a formulas, then you should highlight that range from the get go, and not include that in your monitor range for this code.
Hope this helps you.
Tom Urtis
Posted by Tom Urtis on January 28, 2002 11:55 PM
One more thing...
Forgot to mention, if this is a one-time or infrequent activity, instead of VBA, you could highlight the range of interest, then click on Edit > GoTo > Special, select the Formulas radio button, and hit OK, which will select all the cells containing formulas. Then click on Format > Cells, and choose your pattern or font preferences.
Tom Urtis
Posted by Juan Pablo G. on January 29, 2002 7:27 AM
Re: One more thing...
One more option, check out Tips of the Day for Tuesday, December 18, 2001
http://www.mrexcel.com/weblog/weblog.shtml">http://www.mrexcel.com/weblog/weblog.shtml
Juan Pablo G.
Posted by coline on January 29, 2002 2:49 PM
Thanks! It all worked like a charm.
http://www.mrexcel.com/weblog/weblog.shtml">http://www.mrexcel.com/weblog/weblog.shtml Juan Pablo G. : Forgot to mention, if this is a one-time or infrequent activity, instead of VBA, you could highlight the range of interest, then click on Edit > GoTo > Special, select the Formulas radio button, and hit OK, which will select all the cells containing formulas. Then click on Format > Cells, and choose your pattern or font preferences. : Tom Urtis :
