Results 1 to 6 of 6

Thread: Conditional Formatting comparing one cell to another

  1. #1

    Conditional Formatting comparing one cell to another



    Register for a FREE account, and/
    or Log in to avoid these ads!

    How do I set conditional formatting for the cells in Column B so they turn a color when the value goes below the first Column (Column A)?


    I tried all sorts of stuff, the only thing I could make work on ONE cell is to
    1. select the individual cell,
    2. click conditional formatting,
    3. choose "classic" style
    4. format only cells that contain
    5. cell value
    6. less than
    7. and i chose the cell in column A.
    8. Format, RED


    I COULDN"T Apply this to the the whole column, what i tried to do is select the entire column B, set those conditional formats to that above and then select the entire column A - got an error saying that I might want to use a formula, blah blah blah and it wouldn't work.


    I'm sure theres a simple way of doing this, I just can't figure it out! and there's no way I'm doing this on each cell individually.

    Anyone know a formula I can just paste into the cell and copy it down the column? Or the steps in conditional formatting?

    PS I have Mac Excel 2011

  2. #2

    Post

    OK simply put
    Column A Column B
    100 40
    200 100
    300 150
    400 200
    600 700
    500 200
    900 1000

    How do I set conditional formatting so If Column B1<A1 Then Red, If Column B2<A2 Then Red and so on. You can't copy and paste the formatting like you can a formula, so how do i apply this condition to anything that gets entered in column B?

    Help!

  3. #3
    Quote Originally Posted by ladygogo View Post
    OK simply put
    Column A Column B
    100 40
    200 100
    300 150
    400 200
    600 700
    500 200
    900 1000

    How do I set conditional formatting so If Column B1<A1 Then Red, If Column B2<A2 Then Red and so on. You can't copy and paste the formatting like you can a formula, so how do i apply this condition to anything that gets entered in column B?

    Help!

    follow these steps:

    1. Select the range of cells in column B you want the formatting applied to
    2. click conditional formatting ( under home tab )
    3. select Highlight Cells Rules > less than
    4. Enter =A1 in the source box on left side.
    5. Click OK

    You are done.

  4. #4
    Quote Originally Posted by tommyt61 View Post
    follow these steps:

    1. Select the range of cells in column B you want the formatting applied to
    2. click conditional formatting ( under home tab )
    3. select Highlight Cells Rules > less than
    4. Enter =A1 in the source box on left side.
    5. Click OK

    You are done.
    woah, why did i make it so complicated.

  5. #5
    you know what it was, i have a lot of blank cells in that row and it was applying the red formatting to those as well, so i added a first condition to "no fill with color format" if blank.I wish i could say If blank, don't apply the condition, but that doesn't seem to exist. Oh excel i love you but you're bringing me down.

    Thanks tommy!

  6. #6
    Here is a formula to not add format to the blank cells in your column. select your current range you have formatting set . go into conditional formatting and delete your existing rule(s)


    1. Confirm your range in column B is selected
    2. Click Conditional Formatting
    3. Select New Rule ( located at bottom of the open box)
    4. Select >Use formula to determine which cells to format
    5. Paste this code into the source box =AND(B1<>"",A1>B1)
    6. Click format button to set the fill and font color you want for your condition if it is met.

    Done.
    Last edited by tommyt61; 2012-10-22 at 02:34 PM.

Posting Permissions

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