Formula or reference WITHIN the selected worksheet - BLACK
Reference outside of current worksheet - GREEN
Reference outside of current workbook - RED
These conditions are not exhaustive since a formula can exist that does not meet any of those conditions (eg =4*2)
The conditions are also not mutually exclusive - see my first paragraph in post #3. To emphasise - a single formula can refer to ..
a) a cell within the current sheet and a cell in another workbook
b) a cell in another worksheet of the current workbook and a cell in another workbook
c) a cell within the current sheet and a cell in another worksheet of the current workbook and a cell in another workbook
etc
So, for example, if one of the above examples occur, what colour to use?
Taking a guess that ..
If another workbook is involved at all, use red.
Otherwise if another worksheet is involved at all, use green
Otherwise, use black
.. then this may be some use.
<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> AutoColorSelection()<br> <SPAN style="color:#00007F">Dim</SPAN> cell <SPAN style="color:#00007F">As</SPAN> Range, SCC <SPAN style="color:#00007F">As</SPAN> Range, SCF <SPAN style="color:#00007F">As</SPAN> Range<br> <SPAN style="color:#00007F">Dim</SPAN> CI <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br> <SPAN style="color:#00007F">With</SPAN> Selection<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> SCC = .SpecialCells(xlCellTypeConstants, xlNumbers)<br> <SPAN style="color:#00007F">Set</SPAN> SCF = .SpecialCells(xlCellTypeFormulas)<br> <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> SCC <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> SCC.Font.ColorIndex = 5<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> SCF <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> SCF<br> <SPAN style="color:#00007F">If</SPAN> cell.Formula <SPAN style="color:#00007F">Like</SPAN> "*.xls*]*!*" <SPAN style="color:#00007F">Then</SPAN><br> CI = 3<br> <SPAN style="color:#00007F">ElseIf</SPAN> cell.Formula <SPAN style="color:#00007F">Like</SPAN> "*!*" <SPAN style="color:#00007F">Then</SPAN><br> CI = 50<br> <SPAN style="color:#00007F">Else</SPAN><br> CI = 0<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> cell.Font.ColorIndex = CI<br> <SPAN style="color:#00007F">Next</SPAN> cell<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>