+ Reply to Thread
Results 1 to 8 of 8

Conditional formatting based on individual cell contents, for many cells

  1. #1
    Registered User
    Join Date
    10-12-2013
    Location
    Seattle, USA
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    36

    Question Conditional formatting based on individual cell contents, for many cells

    My spreadsheet has a moderately big array (approx 80 rows by 10 columns) where each cell contains, by default, a formula. Users can over-ride the existing formula by entering a numerical constant.

    I want to use conditional formatting to shade any cell that contains a constant rather than a formula, so that changed cells can be seen at a glance. I can do this OK for any individual cell, but I'd like to avoid having 800 separate conditional formatting rules, one per cell, all essentially identical but each referring to a different cell. (Reason? I'd like to be able to change the shading color without having to edit 800 different rules!) I can't see a way do this - grateful for any guidance!

  2. #2
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: Conditional formatting based on individual cell contents, for many cells

    Hi Ian

    I can't seem to attach anything for some reason but please see below for one solution. It uses VBA to create a function that determines if the value in the cell is a formula or not (ISFORMULA), then you build your conditional formatting around that:

    1. Open VBA (alt + F11)
    2. Find workbook in left panel, right click and insert module.
    3. Paste following code in module:
    Please Login or Register  to view this content.
    4. Close VBA and select range you want to apply conditional formatting.
    5. Go to "Use a formula to determine which cells to format"
    6. Use formula =ISFORMULA(A2)=FALSE {change A2 to top left cell in your highlighted range]
    7. Apply what ever formatting you want (colour cell, make italic etc.)
    8. Click OK.

    You should be sorted after that

    If you have any issues please let me know.
    Attached Files Attached Files
    Last edited by FDibbins; 08-25-2016 at 12:46 AM.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Conditional formatting based on individual cell contents, for many cells

    jeversf To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,013

    Re: Conditional formatting based on individual cell contents, for many cells

    I can do this OK for any individual cell,
    You are doing that with CF?

  5. #5
    Forum Contributor
    Join Date
    01-17-2012
    Location
    Canada
    MS-Off Ver
    Excel 365
    Posts
    221

    Re: Conditional formatting based on individual cell contents, for many cells

    Thank you for that FDibbins...clearly I have been away too long! :D

  6. #6
    Registered User
    Join Date
    10-12-2013
    Location
    Seattle, USA
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    36

    Re: Conditional formatting based on individual cell contents, for many cells

    Quote Originally Posted by FDibbins View Post
    You are doing that with CF?
    Yes, just using the reference of the cell that contains the conditional formatting in the conditional formatting formula. My problem (as I see it) is that the formula has to be different for every cell it's in.

    My apologies to you and to Jeversf - since I posted my original cry for help, the s has hit the f and I simply have not had a minute to return to the issue. I expect to do so next week. Know that I deeply grateful for your suggestions, and will certainly be delving further as soon as I can.

    Very best wishes...

  7. #7
    Forum Contributor
    Join Date
    06-28-2016
    Location
    Pennsylvania, USA
    MS-Off Ver
    2013
    Posts
    308

    Re: Conditional formatting based on individual cell contents, for many cells

    I think you are misunderstanding how to use CF then.

    For instance, if you use Jeversf's VBA solution, you click cell A1 then go to CF. In CF choose the option allowing you to use a formula, then enter:

    =isformula(A1)

    apply it. Now go to change your range, and select all the cells you want to apply this to. So long as you don't lock the cell reference in any way, it will change as it moves.

  8. #8
    Registered User
    Join Date
    10-12-2013
    Location
    Seattle, USA
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    36

    Re: Conditional formatting based on individual cell contents, for many cells

    All responders, thanks for your suggestions. Unfortunately, I'm not able to use ISFORMULA or FORMULATEXT functions because I much prefer using Excel for Mac 2011 (much better interface IMO) and those functions were first added in 2013.

    But (with help) I did come up with a solution that works beautifully for me: I post it here so others can benefit. It makes use of an old XL4 macro language function, GET.CELL, that returns much more information than the CELL function does.

    1. In Insert > Name > Define... I defined the name "CellHasFormula" to be "=GET.CELL(48,INDIRECT("RC",FALSE))".
    2. I created a Conditional Formatting rule that used the following formula to determine whether to apply the shading: "=NOT(CellHasFormula)", and applied that CF rule to hundreds of cells.
    3. Now, every time a user replaces the existing formula in any of those cells with a constant, the cell is highlighted!

    Caution: I believe that there are issues using old XL4 macro commands in names, when you're running later versions of Excel. Not an issue for me though.

+ 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. Conditional formatting based on contents of another cell
    By Kevska in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2015, 09:54 PM
  2. Conditional Formatting based on cell contents?
    By Cremorneguy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-09-2015, 06:29 AM
  3. [SOLVED] Conditional Formatting cells based on another cells contents
    By smls in forum Excel General
    Replies: 5
    Last Post: 12-11-2014, 10:58 AM
  4. Replies: 3
    Last Post: 12-14-2013, 02:52 PM
  5. [SOLVED] Conditional Formatting based on contents in another cell
    By flisters in forum Excel General
    Replies: 3
    Last Post: 03-22-2012, 07:04 AM
  6. Replies: 6
    Last Post: 11-25-2011, 08:41 AM
  7. Replies: 6
    Last Post: 11-21-2010, 08:55 PM

Tags for this Thread

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