Macro that will color selected cells green if they refer to other worksheet

lifeskillz

New Member
Joined
Jul 26, 2011
Messages
5
I am trying to create a macro that will color a selected range of cells green (or any other color for that matter) if the formula in that cell refers to a value in another worksheet.

My current macro colors a cell blue if it has a hardcoded value or black if it refers to another cell within that worksheet. I just don't know how to color the cell green if the formula refers to a cell in another worksheet.

Can anyone help? Thank you!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Seeing as you have the macros in place for everything except the other sheet item, maybe all you'd seemingly need to do is put a condition in your macro if the formual contains an exclamation mark which is what a formula would have if it refers to another sheet.

In casual syntax:

Dim strFormula as String, cell as Range
strFormula = cell.Formula
If instr(strFormula, "!") > 0 then

'and at this point the formula does contain an exclamation,
'so induce the green color here

End If
 
Upvote 0
As a matter of interest, what colour will you make the cell if it refers to a cell within the worksheet AND to a cell in another worksheet?
eg
=B3+Sheet2!J7


And (just playing the devil's advocate) although Tom's suggestion will most likely serve your purpose, note that it is possible that such a method could fail in at least two circumstances that come to mind:

1. A cell could contain a formula where the ! is part of some text, rather than signifying a reference to another sheet so you would get a 'false positive' result.
eg ="You spent $"&C1&" this month!"

2. A cell formula could contain a defined name that refers to another sheet, in which acse you could get a 'false negative'.
 
Upvote 0
My Mappit! addin creates a full mapping file (as a separate workbook) that includes indentifying internal worksheet links, and a 2*2 table summary showing how the sheets link togther
http://www.experts-exchange.com/A_2613.html

If its critical that it is applied to your current workbook then I will extract that part of the code from the addin

Cheers

Dave
 
Upvote 0
Right now I'm using the follow code to color constants and formulas:


Sub AutoColorSelection()
On Error Resume Next
If Selection.SpecialCells(xlCellTypeConstants, 23).Count > 0 Then
With Selection.SpecialCells(xlCellTypeConstants, 23).Font
.ColorIndex = 5
End With
End If
If Selection.SpecialCells(xlCellTypeFormulas, 23).Count > 0 Then
With Selection.SpecialCells(xlCellTypeFormulas, 23).Font
.ColorIndex = 0
End With
End If
End Sub
 
Upvote 0
A simpler version of that would be:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> AutoColorSelection()<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>        .SpecialCells(xlCellTypeConstants, 23).Font.ColorIndex = 5<br>        .SpecialCells(xlCellTypeFormulas, 23).Font.ColorIndex = 0<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">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

But note that this will colour all your formula cells the same, not just the ones that only refer to the current sheet.

Implementing Tom's idea, you could try something like this:

<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<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>        .SpecialCells(xlCellTypeConstants, 23).Font.ColorIndex = 5<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> .SpecialCells(xlCellTypeFormulas, 23)<br>            cell.Font.ColorIndex = IIf(InStr(cell.Formula, "!") > 0, 50, 0)<br>        <SPAN style="color:#00007F">Next</SPAN> cell<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">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
Peter,

Thanks for the quick response. You make a valid point re: the "false positives." Since I'm just using this for personal reasons I'm okay with the false positives. I'm basically looking for a function that is able to color cells with the following conditions:

Constants/Harcoded values - BLUE
Formula or reference WITHIN the selected worksheet - BLACK
Reference outside of current worksheet - GREEN
Reference outside of current workbook - RED

Thanks again!
 
Upvote 0
Peter,

One other question, how can I change your sub so that it colors a cell blue (hardcoded value) only if it is hardcoded/constant AND a number...so it shouldn't color a cell blue if it has text, only a number?

Thanks again!
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top