Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional Format (2002)

    I'm trying to set a conditional format to indicated when a value in a cell is obtained by a formula rather than a straight number. I'm sure this is a simple task (when you know how).

    Any Loungers have any suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format (2002)

    1. Create a custom function in a standard module:

    Function IsFormula(oRange As Range) As Boolean
    IsFormula = oRange.Cells(1).HasFormula
    End Function

    2. Say that the cell you want to format is A1. If necessary, insert an empty column B, then enter the following formula in B1:

    =IsFormula(A1)

    You can hide column B if you like. Note: if you put the function code in your Personal.xls, the formula becomes

    =Personal.xls!IsFormula(A1)

    3. Select the cell(s) you want to format, in this example A1. Then select Format | Conditional Formatting...
    From the first dropdown list, select Formula Is; in the box next to it, enter =(B1=TRUE)
    Click Format... and set the formatting you like, then click OK.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (2002)

    Hans, thanks for the quick response.

    I haven't had much experience with custom functions - I understand the rest. Can you give me some info on creating a custom function.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Conditional Format (2002)

    If you want the function to be available in a single workbook, read the instructions below. If you want it to be available in all workbooks, you must put it in Personal.xls. Read Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> to learn what it is and how to create it. Then continue here.

    Here is how to create the custom function:
    <UL><LI>Activate the Visual Basic Editor (Tools | Macro | Visual Basic Editor or Alt+F11)
    <LI>Make sure that the Project Explorer is visible (Ctrl+R)
    <LI>Click on the workbook you want to store the code in (a specific workbook or Personal.xls)
    <LI>Select Insert | Module
    <LI>Copy the function code from my previous reply, and paste it into the module[/list]That's it! From now on, you can use the function in cell formulas the way I indicated in my previous reply.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Format (2002)

    Thanks very much Hans - Will give it a go!!

Posting Permissions

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