Conditional formatting to identify Linked Cells

Cam Johnston

New Member
Joined
Apr 11, 2013
Messages
10
<!--[if gte mso 9]><xml> <o:OfficeDocumentSettings> <o:AllowPNG/> </o:OfficeDocumentSettings> </xml><![endif]--> Good afternoon, I am trying to simplify usage of a fairly complex spread sheet by helping users understand the various roles of different cells, inputs, linked, formulas, etc, using conditional formatting


I am searching for a way to highlight linked cells. I can do a bit of VBA and have found piece of code to help identify formulas that are totalling using the SUM function.

Public Function HighLink(range) As Boolean

HighLink = if(InStr(range.Formula, "*") > 0 And range.HasFormula

End Function

If have tried to modify this code to identify linked cells with no luck. If this can be modified great. If there is a better approach I am wide open to it.

Thanks in advance for your help
Cam
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-CA</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:EnableOpenTypeKerning/> <w:DontFlipMirrorIndents/> <w:OverrideTableStyleHps/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" DefUnhideWhenUsed="true" DefSemiHidden="true" DefQFormat="false" DefPriority="99" LatentStyleCount="267"> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Normal"/> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="heading 1"/> <w:LsdException Locked="false" Priority="0" QFormat="true" Name="heading 2"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 3"/> <w:LsdException Locked="false" Priority="0" QFormat="true" Name="heading 4"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 5"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 6"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 7"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 8"/> <w:LsdException Locked="false" Priority="9" QFormat="true" Name="heading 9"/> <w:LsdException Locked="false" Priority="39" Name="toc 1"/> <w:LsdException Locked="false" Priority="39" Name="toc 2"/> <w:LsdException Locked="false" Priority="39" Name="toc 3"/> <w:LsdException Locked="false" Priority="39" Name="toc 4"/> <w:LsdException Locked="false" Priority="39" Name="toc 5"/> <w:LsdException Locked="false" Priority="39" Name="toc 6"/> <w:LsdException Locked="false" Priority="39" Name="toc 7"/> <w:LsdException Locked="false" Priority="39" Name="toc 8"/> <w:LsdException Locked="false" Priority="39" Name="toc 9"/> <w:LsdException Locked="false" Priority="0" Name="footnote text"/> <w:LsdException Locked="false" Priority="35" QFormat="true" Name="caption"/> <w:LsdException Locked="false" Priority="0" Name="footnote reference"/> <w:LsdException Locked="false" Priority="0" Name="page number"/> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Title"/> <w:LsdException Locked="false" Priority="1" Name="Default Paragraph Font"/> <w:LsdException Locked="false" Priority="0" Name="Body Text"/> <w:LsdException Locked="false" Priority="0" Name="Body Text Indent"/> <w:LsdException Locked="false" Priority="11" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtitle"/> <w:LsdException Locked="false" Priority="0" Name="Body Text 2"/> <w:LsdException Locked="false" Priority="0" Name="Body Text 3"/> <w:LsdException Locked="false" Priority="0" Name="FollowedHyperlink"/> <w:LsdException Locked="false" Priority="22" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Strong"/> <w:LsdException Locked="false" Priority="20" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Emphasis"/> <w:LsdException Locked="false" Priority="0" SemiHidden="false" UnhideWhenUsed="false" Name="Table Grid"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Placeholder Text"/> <w:LsdException Locked="false" Priority="1" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="No Spacing"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 1"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 1"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 1"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 1"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 1"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 1"/> <w:LsdException Locked="false" UnhideWhenUsed="false" Name="Revision"/> <w:LsdException Locked="false" Priority="34" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="List Paragraph"/> <w:LsdException Locked="false" Priority="29" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Quote"/> <w:LsdException Locked="false" Priority="30" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Quote"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 1"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 1"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 1"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 1"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 1"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 1"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 1"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 1"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 2"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 2"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 2"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 2"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 2"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 2"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 2"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 2"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 2"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 2"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 2"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 2"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 2"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 2"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 3"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 3"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 3"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 3"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 3"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 3"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 3"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 3"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 3"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 3"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 3"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 3"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 3"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 3"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 4"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 4"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 4"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 4"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 4"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 4"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 4"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 4"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 4"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 4"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 4"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 4"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 4"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 4"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 5"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 5"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 5"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 5"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 5"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 5"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 5"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 5"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 5"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 5"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 5"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 5"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 5"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 5"/> <w:LsdException Locked="false" Priority="60" SemiHidden="false" UnhideWhenUsed="false" Name="Light Shading Accent 6"/> <w:LsdException Locked="false" Priority="61" SemiHidden="false" UnhideWhenUsed="false" Name="Light List Accent 6"/> <w:LsdException Locked="false" Priority="62" SemiHidden="false" UnhideWhenUsed="false" Name="Light Grid Accent 6"/> <w:LsdException Locked="false" Priority="63" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 1 Accent 6"/> <w:LsdException Locked="false" Priority="64" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Shading 2 Accent 6"/> <w:LsdException Locked="false" Priority="65" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 1 Accent 6"/> <w:LsdException Locked="false" Priority="66" SemiHidden="false" UnhideWhenUsed="false" Name="Medium List 2 Accent 6"/> <w:LsdException Locked="false" Priority="67" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 1 Accent 6"/> <w:LsdException Locked="false" Priority="68" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 2 Accent 6"/> <w:LsdException Locked="false" Priority="69" SemiHidden="false" UnhideWhenUsed="false" Name="Medium Grid 3 Accent 6"/> <w:LsdException Locked="false" Priority="70" SemiHidden="false" UnhideWhenUsed="false" Name="Dark List Accent 6"/> <w:LsdException Locked="false" Priority="71" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Shading Accent 6"/> <w:LsdException Locked="false" Priority="72" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful List Accent 6"/> <w:LsdException Locked="false" Priority="73" SemiHidden="false" UnhideWhenUsed="false" Name="Colorful Grid Accent 6"/> <w:LsdException Locked="false" Priority="19" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Emphasis"/> <w:LsdException Locked="false" Priority="21" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Emphasis"/> <w:LsdException Locked="false" Priority="31" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Subtle Reference"/> <w:LsdException Locked="false" Priority="32" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Intense Reference"/> <w:LsdException Locked="false" Priority="33" SemiHidden="false" UnhideWhenUsed="false" QFormat="true" Name="Book Title"/> <w:LsdException Locked="false" Priority="37" Name="Bibliography"/> <w:LsdException Locked="false" Priority="39" QFormat="true" Name="TOC Heading"/> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin-top:0in; mso-para-margin-right:0in; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-fareast-language:EN-US;} </style> <![endif]-->
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi Cam and Welcome to the Board,

Please clarify specifically what you mean by "linked cells".
Does that mean any cells with a reference to another cell or range, or just links to other workbooks, or something else?
How would you handle the overlap of some cells that are Formulas with Links and others have Formulas without Links?
 
Upvote 0
Hi Jerry
Thanks for taking the time to respond.

"Linked Cells" are cells that refer to another cell (maybe a range) and nothing else i.e (=B2). There will be no operands (i.e. +, -, /, *) in the linked cell. Thus, and this is where I was having trouble, needs to filter out cells that contain formulas and a linked cell i.e. (=B2*2).

Hope this helps and would really appreciate any insights and assistance you can provide

cheers
Cam
 
Upvote 0
Cam, For most simple cases like the one you show, that isn't too difficult to do using VBA.

This would be much more complex if it needs to handle "atypical" formula and references.

Atypical scenarios could be:
References to other sheets or workbooks whose names include operands or special characters
Formulas that use the space character as an operand for interersection

Also not sure how you would want to handle these (which of these is considered a Link by your definition?):
Named Ranges: =MyRange
Contiguous Ranges: =A1:A10
Non-Contiguous Ranges: =H6,J10,K7
Parentheses with no functions: =(K2)

I'm sure there are other interesting combinations too.

For the use you describe, it doesn't sound like you need to handle all scenarios, but I thought I'd caveot this before suggesting anything.
 
Upvote 0
Hi Jerry, great clarification(s)

For my needs right now I think I am out the a typical you have defined.

The ranges you identified in your 4th paragraph ... I would nice if they could be handled. if this gets to complex then they can be dropped :)

I look forward to your suggestions

cheers
 
Upvote 0
Hi Cam,

This is an interesting puzzle. It's difficult not to get drawn down the rabbit's hole trying to handle every conceivable formula expression.

Fortunately, you have a some flexibility in what you define as a cell with a "linked cells" formula.

If you make the official definition of that "any cell that returns True when referenced by this UDF" -
then this code will work flawlessly! ;)

Place the code below into a standard code module and then define a conditional formatting rule like:
=HighLink(A1)
Applies To: $A$1:$Z$100

Code:
Public Function HighLink(rCell As Range) As Variant
'---Returns TRUE if rCell has a formula meeting this definition of a "Link"
'   1. Has a single reference to a workbook range using range address(s)
'   2. Has no worksheet functions
'   3. Has no Arithmetic operators except possibly leading "+"
'   4. Has no Comparison operators except a leading "="
'   5. Reference to a Defined name is not a link under this definition
'   6. The single reference may be the result of a combination of address references
'      a. (A1:B10)      'using any combination of reference operators
'      b. (A1,B4,C6)
'      c. (B1:B20 A6:D7)
'   7. The reference may be to another worksheet
'   8. The reference may be to another workbook

'--Currently, this code does not test for:
'   A. All Worksheet naming requirements
'   B. All Workbook naming requirements
'   C. All Filepath naming requirements
'   C. Whether referred to range exists (Filename and sheetname)

    Dim sFormula As String
    Dim rTest As Range

    
    '--Set default return value for Exit Function statements
    HighLink = False

    
    On Error GoTo ErrorHandler

    
    '--input argument must be a reference to a single cell
    If rCell.CountLarge > 1 Then Exit Function


    '--must be a formula
    If Not rCell.HasFormula Then Exit Function

    
    '--strip leading "="
    sFormula = Mid(rCell.Formula, 2)

    
    '--optional: To allow =+A1 syntax as "Link"
    While Left(sFormula, 1) = "+"
        sFormula = Mid(sFormula, 2)
    Wend

       
    '--optional: Strip outer parentheses to allow =(A1) syntax as "Link"
    While sFormula Like "(*)"
        sFormula = Mid(sFormula, 2, Len(sFormula) - 2)
    Wend

       
    '--test for bracketed Workbook file Reference
    If sFormula Like "*[[]?*[]]*!?*" Then
        '--either has workbook ref or is invalid as "Link"
        '--test only one "[", one "]", order of brackets was confirmed by pattern match
        If Not ( _
            InStr(1, sFormula, "[") = InStrRev(sFormula, "[") And _
            InStr(1, sFormula, "]") = InStrRev(sFormula, "]")) _
            Then Exit Function

            
        '--strip path[workbook] leaving "sheetname!range" if valid
        sFormula = Split(sFormula, "]")(1)
        '--Optional: could add test whether Split(sFormula, "!")(0)
        '     meets workbook filename requirements
    End If

    
    '--if valid link, the remaining structure is either:
    '       sheetName!rangeAddress  or:  rangeAddress

        
    '--strip sheet reference if any
    If sFormula Like "?*!?*" Then _
        sFormula = Split(sFormula, "!")(UBound(Split(sFormula, "!")))
    '--Optional: could add test whether removed substring meets sheetname requirements

        
    '--if valid link, remaining string should work as a range reference in this workbook
    On Error Resume Next
    Set rTest = Range(sFormula)
    If rTest Is Nothing Then Exit Function
    On Error GoTo ErrorHandler

    
    '--if valid link, remaining string should be a valid range address and not a defined name
    With Application
        '--defined name won't change when converted from absolute to relative, range address will
        If .ConvertFormula(sFormula, xlA1, xlA1, xlAbsolute) <> _
            .ConvertFormula(sFormula, xlA1, xlA1, xlRelative) Then _
            HighLink = True
    End With


    Set rTest = Nothing
    Exit Function
ErrorHandler:
    HighLink = CVErr(xlErrValue)
End Function

Be aware that Conditional Formatting is Volatile, meaning that the formulas recalculate anytime anything on the sheet recalculates. If you apply this CF to large ranges, you might experience some lag.

If that were to happen, consider using a macro that you can call as needed, to update the fill color of a range of cells using this core code.

Please let me know if this works for your educational purposes.
 
Last edited:
Upvote 0
Hi Jerry, Wow this looks great :)

An I understand what you mean by the rabbit hole :)

The VB seems to working well, there seems to be one unintended consequence which is that some cells appearing not to meet the criteria are being highlighted.

The pic below shows
- blue where things are working
- red where they are not
- green and illustration of a specific cell

Here is the link in dropbox
https://dl.dropboxusercontent.com/u/23209965/4 Jerry/Capture 4 Jerry.PNG

I have looked to see if there is anything in the cells that maybe triggering something, I have found nothing.

Just to make sure that I am not screwing up I have included two other conditions the execute prior to HighLink.

The first is a VB identifying formulas:

Public Function HighSum(range) As Boolean

HighSum = InStr(range.Formula, "SUM") > 0 And range.HasFormula

End Function

The second is formula identifying inputs IE manaul entries i.e A1 "=400"
=IF(CellHasText,0,IF(ISBLANK(INDIRECT("rc",FALSE)),0,IF(ISTEXT(INDIRECT("rc",FALSE)),0,1)))

Hopefully neither one of these is complicating things.

I did delete the other two conditions and test.
1) HighLink does highlight the the "linked" cells
2) it also highlights the Manual input cells
3) and it also is still highlighting selective cells that appear empty as shared in the picture.

I trust his helps and that this is not too much more effort

Thank you so much for your help it is very much appreciated.

cheers
 
Upvote 0
Hi Cam, Check to see if you have alignment between the ranges referenced in the CF formula and the CF "Applies To:"

My example had:
=HighLink(A1)
Applies To: $A$1:$Z$100

If you have something like this:
=HighLink(A1)
Applies To: $AA$62:$AZ$100

...then the cells being evalulated will be offset from the cells being highlighted. ie $AA$62 will highlight based on the formula in $A$1

When you define the CF rule, first select the cell that will be the upper leftmost cell in the Applies To Range (let's say $AA$62)
Then adjust your entries
=HighLink(AA62)
Applies To: $AA$62:$AZ$100

Those other two CF Rules that you tried adding are unnecessary and might give you incorrect results.

I'll post a separate explaination on that, but first let's figure out how to get the intended cells highlighting.
 
Upvote 0
Hi Jerry, you were correct, rookie mistake on my part. Thank you for catching it so quickly and patiently.

Things seems to be working perfectly now.

Thnk yo so much for all of your insight and assistance.

Cam

PS - is there a good resource you can point out for pragmatically learning VB with excel :)
 
Upvote 0
Cam, I'm glad it was something easy to fix. :)

Regarding learning VBA for Excel, there's no shortage of information available on the topic. Since each person learns differently I recommend using whatever method or tools have worked well for you when you've learned other technology.
Some people learn best from a tutorial book or taking a class; while others do best experimenting on their own using the resources like the Excel Developer Reference (Excel VBA Help) and online examples.

If you learn well from books, I think John Walkenbach's "Excel 2010 Power Programming with VBA" is a good one.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
Members
448,533
Latest member
thietbibeboiwasaco

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