Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: color cells with formulas

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again!

    I want to apply special formatting to cells that contain formulas.
    I think u've already answered this but i don't know where to locate the answer. Can u please help me?
    Thanx

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-02-26 02:38, Joanna_gr wrote:
    Hi again!

    I want to apply special formatting to cells that contain formulas.
    I think u've already answered this but i don't know where to locate the answer. Can u please help me?
    Thanx
    Joanna,

    You need a user-defined function which can test whether a cell houses a formula:

    VBA is not my PL (neither COBOL, by the way), but you can insert the following code in a module in your WB:

    Function HoldsFormula(Cell) As Boolean
    HoldsFormula = Cell.Range("A1").HasFormula
    End Function

    Now you can apply Cond Format to, B1:B10, a range of interest:

    Select B1:B10;
    Activate Format|Conditional Formatting;
    Choose "Formula Is" for Condition 1;
    Enter in the formula box:

    =HoldsFormula(B1) [ important: don't freeze B1 by using F4 -- that is, no $$ here ]

    Activate Format;
    Choose a color on the Patterns tab;
    Click OK, OK.

    Aladin

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Someone did post a clever answer to this some time ago. I have forgotten the author so I am sorry I can't give credit. As I remember it, it went:

    Select all cells
    At the toolbar select Insert,Name, Define
    In "Names in Workbook" type "formula" (do NOT use quotation marks)
    In "Refers to.." type GET.CELL(48,A1)
    with all cells still selected go to Format, Conditional Formatting
    Change "Cell value is" to "Formula is" and type in = "formula" (do NOT use quotation marks)
    Select a coloured pattern and click OK
    This should highlight all cells with formulas
    Check conditional formattting again to ensure there are no quote marks around the word formula that you typed in.
    regards
    Derek


  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Athens Greece
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanx Derek, Aladin

    Derek, it worked. thank u very much. Can u please explain to me what CELL(48,A1) refers to? thanx again.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Perth Australia
    Posts
    1,584
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Joanna
    I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
    Good Luck
    Derek

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-02-26 05:00, Derek wrote:
    Hi Joanna
    I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
    Good Luck
    Derek
    Here is a link to get more info:

    http://www.j-walk.com/ss/excel/usertips/tip045.htm

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Derek, thanks... this was me. Posted as a Tip of the Day, for whoever needs it again.

    This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !

    http://www.mrexcel.com/weblog/archiv..._webloga.shtml

    Tip for Tuesday, Dec 18th. 2001.

    On 2002-02-26 05:00, Derek wrote:
    Hi Joanna
    I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
    Good Luck
    Derek
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    I noticed that too late!


    On 2002-02-26 05:49, Juan Pablo G. wrote:
    Derek, thanks... this was me. Posted as a Tip of the Day, for whoever needs it again.

    This uses Excel 4 macros (Get.Cell is a "command"), and the 48 is just a parameter... there are a lot of parameters for cells (60 or 70 if i'm correct), and each one has it's own meaning !

    http://www.mrexcel.com/weblog/archiv..._webloga.shtml

    Tip for Tuesday, Dec 18th. 2001.

    On 2002-02-26 05:00, Derek wrote:
    Hi Joanna
    I must be honest I haven't the faintest idea what =GET.CELL(48,A1) really means. It was posted sometime ago by one of the gurus, and I'm sure if he/she reads this they will be happy to tell us. I tried different numbers from 48 to see if other magic things happened, but no, it seems 48 has some kind of special significance. Guys here seem willing to share all sorts of amazing stuff and I have received lots of help (I wish I could pretend I understand it all)
    Good Luck
    Derek

  9. #9

    Join Date
    Feb 2002
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I would not recommend using this method of identifying cells with formulas.
    Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.

    I use these macros to identify formulas and to remove the identification :-


    Sub IdentifyFormulae_Add()
    Dim ws As Worksheet
    For Each ws In ActiveWindow.SelectedSheets
    With ws.UsedRange
    'Remove cell highlights (in case there _
    are any cells highlighted but without _
    formulae)
    .Interior.ColorIndex = 0
    'Identify Formulae
    On Error GoTo e 'Error handler if there are no formula cells
    .SpecialCells(xlFormulas).Interior.ColorIndex = 20
    End With
    Range("A1").Select
    Next
    Exit Sub
    e: MsgBox "There are no cells with formulas"
    End Sub

    Sub IdentifyFormulae_Remove()
    'Remove Identify Formulae (removes all _
    cell highlights in the selection)
    Selection.Interior.ColorIndex = 0
    Range("A1").Select
    End Sub

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,045
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    8 Thread(s)

    Default

    On 2002-02-26 06:29, Autolycus wrote:
    I would not recommend using this method of identifying cells with formulas.
    Cells that have this conditional formatting cannot be copied and pasted to other worksheets and attempting to do so might cause Excel to crash.
    That's interesting. Thanks pointing out.

    Aladin

Some videos you may like

User Tag List

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
  •