Automatic Formatting for Cells with Formulas

Hi,

Without VBA, it gets tricky ...


http://www.mrexcel.com/board2/viewtopic.php?t=21312



Otherwise you can use VBA. That might look something like ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">If</SPAN> Target.HasFormula <SPAN style="color:#00007F">Or</SPAN> Target.HasArray <SPAN style="color:#00007F">Then</SPAN>
        Target.Interior.ColorIndex = 8
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


HTH

If you want to color all the cells in an existing range that contain formulas, (using VBA), you can do something like this. Hope it helps,
Dan

You know, even another one, probably faster ...


<font face=Tahoma New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> ColorSpecialCells()
    <SPAN style="color:#00007F">With</SPAN> Range("A2:E10")
        .SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = 8
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


I am partial to looping through cells as Dan has, there's always just so much you can do. These should all work though.

(Hey Dan! Howzit!!)

With this UDF:


You can use a Conditional Formatting formula like:

=HasFormula(A1)

Good Zack. How's things with you?
I think you're right too. The SpecialCells method would be faster. (Noticeably so depending on the amount of data.) Good call.

Dan