Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    May 2002
    Location
    California, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Coloring in Excel (2000 Professional)

    Is it possible to put a formula in to automatically re-color a cell (background) if the information comes from a certain other sheet. The current formula is a SUM IF formula and would stil be needed, but am having to go in and highlight manually each cell that has brought in the info. If so, what is the formula, or how would you set it up? The color required is Orange (second row, second left). Thanks for the help.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Coloring in Excel (2000 Professional)

    Auto recoloring can be done with conditional formatting, though I am NOT completely sure what you want to key on.

    If you are trying to trigger based on the FORMULA in the cell and to see if the formula is LINKED to another sheet, you could create a custom function to examine the formula and look for a particular sheet.

    For example this added into a module:
    <pre>Function IsReferenced(rCell As Range, sSheet As String) As Boolean
    IsReferenced = InStr(UCase(rCell.Cells(1).Formula), UCase(sSheet))
    End Function</pre>


    Then use conditional formatting:
    Select (eg) A1:G15
    Format - conditional formatting..
    Formula is:
    <pre>=isreferenced(A1,"sheet2")</pre>

    <format>
    set an appropriate color, etc
    Now any cell with a ref to sheet2 will be colored.

    Steve

Posting Permissions

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