Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    format if cell contains a formula (excel97)

    Is there a way through conditional formatting to have all the cells with formulas in them to be formatted differently
    (make the cell blue) than other cells that may just contain a number or text (keep them un-filled)

  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: format if cell contains a formula (excel97)

    Create this user defined formula in a module:
    <pre>Function HasFormula(rCell As Range)
    HasFormula = rCell.Cells(1).HasFormula
    End Function</pre>


    Then select the cells to format (eg A1:c10)
    format - conditional forma
    formula is:
    =hasformula(a1)
    <format>
    Patterns(tab)
    select blue
    <ok><ok>

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: format if cell contains a formula (excel97)

    Here is another method that does not rely on a User Defined Function.

    Follow these steps to shade all cells with formulas:

    1. Select Insert, Name, Define.
    2. In the Define Name dialog box, enter the following in the
    Regards,
    Rudi

  4. #4
    3 Star Lounger
    Join Date
    May 2002
    Location
    Mpls, Minnesota, USA
    Posts
    271
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format if cell contains a formula (excel97)

    Rudi,

    A thing of beauty!

    Chuck
    Chuck Reimer
    I'm from the Government and I'm here to help...

  5. #5
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format if cell contains a formula (excel97)

    Thank you very much for both of the methods.

    It's always good to know the many different ways to skin a cat*


    Regards PaulGD

    *No cats were harmed in the re-creation and successful use of these two methods of turning cells with formula blue.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: format if cell contains a formula (excel97)

    Rudi, would you be good enough to explain the hard bit, ie...

    Then enter the following formula in the "Refers to" box:
    =GET.CELL(48, INDIRECT("rc", FALSE))

    Thanks!

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: format if cell contains a formula (excel97)

    I will answer on Rudi's behalf as I know he works in a lot of satellite locations without WWW access. If you have a look at Chip Pearson's site it gives a great description of The Call Function which will give you a good background to the function and its parts.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Rudi, I hope you don't mind stealing your fire and adding to your excellent suggestion
    Jerry

  8. #8
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: format if cell contains a formula (excel97)

    Tx for the replies Jezza: You make a great secretary! I may just hire you permanently! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    The GET.CELL formula is also documented in the Excel forum. Running a search on the function should reveal further info already discussed in the lounge. See Jan Karels reply here: <post:=428,785>post 428,785</post:>
    Cheers all!
    Regards,
    Rudi

Posting Permissions

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