+ Reply to Thread
Results 1 to 9 of 9

Trying to have conditional formatting based off of the value in a different cell

  1. #1
    Registered User
    Join Date
    12-01-2011
    Location
    el pueblo de nuestra senora la reina de
    MS-Off Ver
    Excel 365
    Posts
    20

    Trying to have conditional formatting based off of the value in a different cell

    I have about 60 rows of 6 columns with numbers in most of them.

    I want to highlight each cell of column E, based on the numbers that are in columns C and D (D will always be higher than C). If Column E is higher than column C but lower than D it should be one color. But If E is higher then D it should be another color, and if it is lower then C it should be a third color

    I am assuming it can be done with conditional formatting, but I have never done conditional formating without it being a fixed number.

    Thank you

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to have conditional formatting based off of the value in a different cell

    Any chance you can attach a sample Excel workbook? The yellow banner at the top of the screen tells you how to.

    Pete

  3. #3
    Registered User
    Join Date
    12-01-2011
    Location
    el pueblo de nuestra senora la reina de
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Trying to have conditional formatting based off of the value in a different cell

    Here is an attached example.
    So in this example, I would want:
    E2 to be Green (because it is higher than D2)
    E3 to be Red (because it is higher than C3 but lower than D3)
    E4 to be Green
    E5 to be Yellow (because it is lower than c5)
    E6 to be Red
    E7 to be Yellow

    Column D will Always be higher than column C

    Thank you
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to have conditional formatting based off of the value in a different cell

    Select all the cells from E2 down (to E7 in your example file), then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

    =AND(E2>C2,E2>D2)

    Click on the Format button | Fill tab and choose green, then OK your way out. With the cells still highlighted you can repeat, but use this formula:

    =AND(E2>C2,E2<=D2)

    and choose red for this condition. Repeat once more and use this formula:

    =E2<=C2

    and set this one to yellow. Note that you don't need the AND for this third formula if you can guarantee that D2 is always larger than (or equal to) C2.

    When you close the dialogue box you will find that Excel will automatically adjust the cell references to suit all the cells that were selected, so you can do them all in this one operation.

    Hope this helps.

    Pete

  5. #5
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to have conditional formatting based off of the value in a different cell

    I've just noticed that you have a location in what looks like Spanish, so you may need to use semicolons ( ; ) instead of commas ( , ) in the formulae, depending on your regional settings.

    Hope this helps.

    Pete

  6. #6
    Registered User
    Join Date
    12-01-2011
    Location
    el pueblo de nuestra senora la reina de
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Trying to have conditional formatting based off of the value in a different cell

    Thank you, I tried it but see attached the colors aren't reflecting properly.
    I am getting
    E2 is Red instead of Green
    E3 is Green instead of Red
    E4 is Yellow instead of Green
    E5 is Red instead of Yellow
    E6 is Yellow instead of Red
    E7 is Yellow when it is supposed to be Yellow.

    So its not even that the colors are mixed up. One time there is a yellow instead of a red and another time its a green instead of red.

    Re the spanish, thank you for the heads up.

    Thank you
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to have conditional formatting based off of the value in a different cell

    I've set this up in the attached file. Does this do what you want?

    By the way, you already had a condition set up for just E2, so I deleted that rule.

    Pete
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    12-01-2011
    Location
    el pueblo de nuestra senora la reina de
    MS-Off Ver
    Excel 365
    Posts
    20

    Re: Trying to have conditional formatting based off of the value in a different cell

    Yes thats perfect. I will test it on the full sheet and see how it reflects.

    Thank you

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    20,978

    Re: Trying to have conditional formatting based off of the value in a different cell

    That's good to hear.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Replies: 2
    Last Post: 09-02-2018, 01:09 PM
  2. Conditional Formatting based on more than one cell.
    By pezz11 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-04-2017, 11:10 AM
  3. Replies: 1
    Last Post: 10-04-2016, 05:17 AM
  4. Conditional formatting based on more than one cell
    By Roan242 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-27-2014, 07:18 AM
  5. Conditional Formatting via VBA: Change formatting in range based on value of each cell
    By ralphjmedia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-12-2013, 10:37 AM
  6. Replies: 4
    Last Post: 01-06-2012, 05:07 PM
  7. Conditional Formatting based on value of another cell
    By pickledmuffin in forum Excel General
    Replies: 3
    Last Post: 05-19-2008, 06:40 PM

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