+ Reply to Thread
Results 1 to 3 of 3

automatically color formulas

  1. #1
    Registered User
    Join Date
    06-24-2015
    Location
    Johannesburg
    MS-Off Ver
    2013
    Posts
    43

    automatically color formulas

    Hello Chaps--

    I need your guidance please.


    I am an amateur at excel and face a problem that i am sure others also face.
    AS i am drawing the worksheet I often want to "clear contents" in an area only to find that i have cleared a formula by mistake.


    Is it possible to create a rule or something which will automatically change the background color of a cell with a formula? As the formula is entered?

    many thanks
    Fred

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: automatically color formulas

    If you're using Excel 2013 or later try this...

    Let's assume the range of interest is A1:A5.

    Select the range A1:A5 starting from cell A1.

    Goto the Home tab>Styles>Conditional Formatting>
    Manage rules>New rule>Use a formula to determine
    which cells to format

    Enter this formula in the box below:

    =ISFORMULA(A1)

    Click the Format button
    Select the Fill tab and select your desired color
    OK out
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Contributor
    Join Date
    12-18-2016
    Location
    London
    MS-Off Ver
    Microsoft 2013
    Posts
    145

    Re: automatically color formulas

    1. Open your worksheet and go to click Formulas > Name Manager
    2. In the popped out Name Manager dialog box, click New button.
    3. And in the New Name dialog box, do the following operations:
    (1.) Type a name for your defined name formula, in this example, I will input Formatformulas;
    (2.) Choose Workbook from the Scope drop down list;
    (3.) Enter this formula =GET.CELL(48,INDIRECT("rc",FALSE)) into the Refers to field.
    4. Then click OK to return to the former dialog and close it.
    5. Then select your range or the whole worksheet that you want to highlight the formula cells.
    6. Under Home tab, click Conditional Formatting > New Rule
    7. In the New Formatting Rule dialog, click Use a formula to determine which cells to format option, and enter this formula =Formatformulas (Formatformulas is your defined name in step 3), you can change it to your own defined name.
    8.Then click Format button, set your formatting under Fill tab
    9. And then click OK > OK to finish the operations, and all the cells with formula will be highlighted.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to automatically sum based upon interior fill color when the fill color changes
    By Murman01 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 02-10-2017, 10:41 AM
  2. Change tab color automatically
    By strgrl14 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2013, 04:35 PM
  3. Replies: 3
    Last Post: 07-08-2010, 06:48 PM
  4. Color Cells Automatically..
    By newbie1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-28-2008, 05:18 AM
  5. [SOLVED] how to automatically color a cell
    By jacko in forum Excel General
    Replies: 3
    Last Post: 05-28-2005, 02:05 AM
  6. [SOLVED] Color a cell and a value is automatically assigned to that color.
    By Bossi in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 05-03-2005, 01:06 AM
  7. Replies: 2
    Last Post: 02-21-2005, 08:06 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1