+ Reply to Thread
Results 1 to 6 of 6

How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

  1. #1
    Registered User
    Join Date
    11-15-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Unhappy How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    I have to compare two different cells and I want one of three different colors to appear and fill a third cell: less than (Green), equal to (Pastel Yellow), and more than (Red). I really have tried to put something together but I just am not familiar enough with the formulas to even know which one I am supposed to use. Every time I try to set something up I either get something wrong and it does not allow the formula to be input at all due to error or it does not give the proper output.

    Cell 1 = 1
    Cell 2 = 2
    Cell 3 = 2 is more than 1 (Red Fill)

    Cell 2 has to be compared with cell 1; basically is cell 2 greater than/less than/or equal to cell 1. Once the results are determined to be less than, equal, or greater than I want Cell 3 to be filled green, pastel yellow, or red respectively. How do I do this with one formula? Is that possible?

    EDIT: Once the value for cell one or two has been deleted I want the shading for cell 3 to go back to no shading at all. Is that possible in addition to the other formula?
    Last edited by MAB88; 11-15-2016 at 07:33 PM. Reason: Forgot to Add last Conditional Goal

  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: How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    You can do this with conditional formatting.

    Let's assume the cells are A1, A2 and A3.

    Select cell A3

    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:

    =AND(COUNT(A1,A2)=2,A2<A1)

    Click the Format button
    Select the desired fill color green
    OK out

    Repeat the process for the other 2 conditions using these formulas:

    Yellow: =AND(COUNT(A1,A2)=2,A2=A1)

    Red: =AND(COUNT(A1,A2)=2,A2>A1)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    11-15-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    Thanks, that's working great so far! Appreciate it. Is there a way to put in any number for cells A1 and A2 and achieve the same result?

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

    Re: How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    Quote Originally Posted by MAB88 View Post
    Is there a way to put in any number for cells A1 and A2 and achieve the same result?
    Not sure what that means?

    If you change the numbers in A1 and/or A2 the cell color in A3 will change accordingly.

  5. #5
    Registered User
    Join Date
    11-15-2016
    Location
    United States
    MS-Off Ver
    Microsoft Office Professional Plus 2010
    Posts
    18

    Re: How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    I must have made a mistake and put a manual fill into the cell because it was not changing color. I was able to fix it and see it is responding in real time to the numbers I put in. Thank you so much for your help!

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

    Re: How to Use Formulas: Compare Two Cells' Values and Color Fill a Third Cell

    You're welcome. Thanks for the feedback!

+ 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. [SOLVED] How to fill color cells based on another cell's fill color?
    By putritersenyum in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-22-2016, 08:58 AM
  2. [SOLVED] Change a cells fill color. VBA to make another cell the same color and also changes.
    By PeteABC123 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-23-2016, 12:34 PM
  3. change fill color of a range of cells based on color of a cell?
    By DarMelNel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-20-2014, 04:48 PM
  4. Referencing cell color fill in formulas
    By WannaBBurly in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-02-2013, 05:41 PM
  5. How to color-fill blank cells, then once data is entered cell is no-fill
    By hatemail13 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-28-2013, 07:57 PM
  6. [SOLVED] Compare Worksheets: Transcribe adjorning cells and Insert value based on Fill Color
    By ub3rm0nk3y in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-25-2013, 07:18 PM
  7. Replies: 3
    Last Post: 07-08-2010, 06:48 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