+ Reply to Thread
Results 1 to 8 of 8

Apply conditional formatting to a cell that contains a formula

  1. #1
    Registered User
    Join Date
    11-15-2014
    Location
    Bournemouth
    MS-Off Ver
    2010
    Posts
    8

    Apply conditional formatting to a cell that contains a formula

    Hi all,

    I'm having problems applying conditional formatting to a row of celss, each cell containg a formula similar to:

    '=IF(b3<10, "0",IF(B3=10, "1"))'

    Now I'm trying to format each cell in this row so that if a cell contains a "0" (as a result of its formula) then it should be red. If a cell contains a "1" (as a result of its formula) then it should be green.

    The problem I have is that when I use conditional formating (format only cells that contain> cell value =...) - it doesn't work - presumably because the cell value isn't "0" or "1" but is instead the entire formula.

    So I try using (format cells that contain>specific text =..). This works fine...until I save and then reopen the workbook - at which point all conditional formatting rules have been deleted and back to square one!

    Can anyone point out what I'm doing wrong/what I can do to correct this?

    Also - a similar/related problem - there is a macro in the workbook that takes data from the aforementioned row of cells and generates a pie chart. This worked fine when I manually input numbers into the cells (before I had the bright idea if using the 'IF' formulas to save me analysing an extract of data from another workbook and manually adding in the "0"'s ot the "1"'s). Now that I've implemented the 'IF' formulas into the relevant cells - the macro won't work - again: I'm guessing this is because it can't see/use the cells formula output value but instead just sees the formula. Any help?

    Many thanks in advance if anyone can spare a thought.
    Last edited by abacus78; 11-19-2014 at 04:57 PM. Reason: typos

  2. #2
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Apply conditional formatting to a cell that contains a formula

    is it possible the reason they aren't working is because you are setting the "value" in the cell to "0" (not a number) or "1" (also not a number) and in the conditional formatting you are setting it Zero or One

    just a thought
    Ernest

    Please consider adding a * if I helped

    Nothing drives me crazy - I'm always close enough to walk....

  3. #3
    Registered User
    Join Date
    11-15-2014
    Location
    Bournemouth
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply conditional formatting to a cell that contains a formula

    Hi judgeh59,


    Thanks for replying.

    I'm not sure I understand what you mean in your response. Are you asking if the cell's that I'm referring to contain either "0", or "1" (including the quote marks)? If this is what you mean, then no; the output of the formula in each cell would be either 0 or 1 (as numerical results) - and when I use conditional formatting - I try formatting cells that contain the value 0 or 1 (without adding quote marks either - I just type a 0 or a 1 in the relevant box). I've attached an example sheet to demonstrate the issue:

    EXAMPLE2.JPG

    Once saved and applied - these rules don't work - no formatting is applied!

  4. #4
    Registered User
    Join Date
    11-15-2014
    Location
    Bournemouth
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply conditional formatting to a cell that contains a formula

    Scrap that - I understand now what you mean: I needed to remove the quote marks from the formula itself. This seems to have fixed the problem - that'll teach me to blindly copy formulas off the internet without really understanding them!

    I'll check if this has sorted my macro issue too then will mark as solved if this is the case.

    Many thanks!

  5. #5
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Apply conditional formatting to a cell that contains a formula

    I'm glad you found it - great job....as a side note....just because it "looks" like a number it doesn't mean it is....if you aren't sure use the ISNUMBER function to determine if is a real number....

    remember formatting only changes HOW it looks NOT the underlying data.....have a great day

  6. #6
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Apply conditional formatting to a cell that contains a formula

    any luck on the Macro?

  7. #7
    Registered User
    Join Date
    11-15-2014
    Location
    Bournemouth
    MS-Off Ver
    2010
    Posts
    8

    Re: Apply conditional formatting to a cell that contains a formula

    Thanks judgeh59, great call!

    Sorry it took a while to get back but yes, the macro works fine now too (I didnīt program the macro, btw! Maybe with a little more swatting though.. lol!)

    Thanks again

  8. #8
    Forum Expert judgeh59's Avatar
    Join Date
    02-07-2013
    Location
    Boise, Idaho
    MS-Off Ver
    Excel 2016
    Posts
    2,310

    Re: Apply conditional formatting to a cell that contains a formula

    glad I could help....

    Thanks for setting the thread to Solved...If you feel I deserve an increase in my reputation, feel free to click on the star in the lower left of the thread. Remember, someday when you help somebody they may add to your rep points, and it could be me...just a thought...

+ 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. Apply Conditional Formatting Formula to Count output
    By mboukili in forum Excel General
    Replies: 7
    Last Post: 06-10-2013, 10:57 AM
  2. Replies: 50
    Last Post: 06-26-2012, 03:59 AM
  3. can you apply Conditional formatting on a cell
    By RMontani in forum Excel General
    Replies: 4
    Last Post: 02-27-2011, 11:00 PM
  4. Conditional Formatting -- Apply to Row if Cell Value does not Matc
    By SteveC in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-21-2006, 12:35 PM
  5. [SOLVED] How does special formula apply to conditional formatting?
    By Frances in forum Excel General
    Replies: 1
    Last Post: 08-22-2005, 09:05 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