Page 1 of 2 12 LastLast
Results 1 to 10 of 17

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

This is a discussion on Macro that will color selected cells green if they refer to other worksheet within the Excel Questions forums, part of the Question Forums category; I am trying to create a macro that will color a selected range of cells green (or any other color ...

  1. #1
    New Member
    Join Date
    Jul 2011
    Posts
    5

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

    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!

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,003

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

    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

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,869

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

    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'.
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  4. #4
    Board Regular
    Join Date
    Feb 2003
    Location
    Adelaide, Australia
    Posts
    426

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

    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

  5. #5
    New Member
    Join Date
    Jul 2011
    Posts
    5

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

    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

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,869

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

    A simpler version of that would be:


    Sub AutoColorSelection()
        With Selection
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants, 23).Font.ColorIndex = 5
            .SpecialCells(xlCellTypeFormulas, 23).Font.ColorIndex = 0
            On Error GoTo 0
        End With
    End Sub


    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:


    Sub AutoColorSelection()
        Dim cell As Range
        
        With Selection
            On Error Resume Next
            .SpecialCells(xlCellTypeConstants, 23).Font.ColorIndex = 5
            For Each cell In .SpecialCells(xlCellTypeFormulas, 23)
                cell.Font.ColorIndex = IIf(InStr(cell.Formula, "!") > 0, 50, 0)
            Next cell
            On Error GoTo 0
        End With
    End Sub
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  7. #7
    New Member
    Join Date
    Jul 2011
    Posts
    5

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

    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!

  8. #8
    New Member
    Join Date
    Jul 2011
    Posts
    5

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

    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!

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    25,869

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

    Quote Originally Posted by lifeskillz View Post
    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.


    Sub AutoColorSelection()
        Dim cell As Range, SCC As Range, SCF As Range
        Dim CI As Long
        
        With Selection
            On Error Resume Next
            Set SCC = .SpecialCells(xlCellTypeConstants, xlNumbers)
            Set SCF = .SpecialCells(xlCellTypeFormulas)
            On Error GoTo 0
        End With
        If Not SCC Is Nothing Then
            SCC.Font.ColorIndex = 5
        End If
        If Not SCF Is Nothing Then
            For Each cell In SCF
                If cell.Formula Like "*.xls*]*!*" Then
                    CI = 3
                ElseIf cell.Formula Like "*!*" Then
                    CI = 50
                Else
                    CI = 0
                End If
                cell.Font.ColorIndex = CI
            Next cell
        End If
    End Sub
    Hope this helps, good luck.
    Peter

    - Read: Posting Guidelines, Forum Rules & FAQs
    - Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste (To test: Test Here)
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the VBHTML Maker

  10. #10
    New Member
    Join Date
    Jul 2011
    Posts
    5

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

    Peter,

    That's perfect, thanks so much for the help.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com