Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Conditional Formatting - Based on other cell Value/format?

This is a discussion on Conditional Formatting - Based on other cell Value/format? within the Excel Questions forums, part of the Question Forums category; Afternoon, Second question of the day regarding conditional formating... Is it possible to alter the format of a range of ...

  1. #1
    Board Regular
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    185

    Default Conditional Formatting - Based on other cell Value/format?

    Afternoon,

    Second question of the day regarding conditional formating...

    Is it possible to alter the format of a range of cells (using conditional formatting) based on a value/format in another cell?

    I have two columns (A/B) which turn dark red if column B>A.

    I would like conditional formatting to turn column C to AO the same color if B>A.

    Unfortunately, I can only seem to get the format to work based on the value of the specific cell in column C to AO.

    Thanks!

  2. #2
    Board Regular yee388's Avatar
    Join Date
    Mar 2004
    Location
    Spring, TX
    Posts
    1,374

    Default Re: Conditional Formatting - Based on other cell Value/forma

    Couldn't you use the same conditional format you used for A/B?

  3. #3
    Board Regular yee388's Avatar
    Join Date
    Mar 2004
    Location
    Spring, TX
    Posts
    1,374

    Default Re: Conditional Formatting - Based on other cell Value/forma

    In C1, make the conditional format

    Formula Is: =$B1>$A1

    and copy it across to AO and down however far you need to go.

  4. #4
    Board Regular
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    185

    Default Re: Conditional Formatting - Based on other cell Value/forma

    Thanks, for some reason I wasn't getting the "Formula Is" bit.

    Have a wrench to throw into this if you guys wouldn't mind giving your input...

    First, is I have four conditions which I would like to evaluate to 3 colors.
    B>A, Green
    B=A, Red
    B= "End" or "Home", Gray

    I'm guessing I can figure out how to take care of the Grey with an OR statement.

    Problem I'm seeing (before even dealing with the "OR" bit of it) is that it won't evaluate to Gray because its interpreting "END" and "Home" numerically and saying oh, its >A, make it Red!

  5. #5
    Board Regular yee388's Avatar
    Join Date
    Mar 2004
    Location
    Spring, TX
    Posts
    1,374

    Default Re: Conditional Formatting - Based on other cell Value/forma

    When conditional formats get complicated, I usually switch to VBA. It would be pretty easy to write a macro for this... or do you need a formula solution?

  6. #6
    Board Regular
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    185

    Default

    Yep, was able to work in an OR statement for one condition, but the other two conditions are still overwriting that condition by mistakenly evaluating "END" and "HOME" numerically...

  7. #7
    Board Regular
    Join Date
    Jan 2004
    Location
    Seattle
    Posts
    185

    Default

    Not familar with VBA at all, was hoping for a quick fix with conditional formatting.

    Only issue i'm having now is that the conditional format is looking at the two text values as numbers...

  8. #8
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default

    This appears to be working for me.

    B>A:
    =AND(ISTEXT(B1)=FALSE,B1>A1)

    B=A:
    =AND(ISTEXT(B1)=FALSE,B1=A1)

    End/Home:
    =AND(ISTEXT(B1)=TRUE,OR(B1="End",B1="Home"))

    However, even if both cells are blank, it will result in the A=B condition.
    Kristy

  9. #9
    Board Regular yee388's Avatar
    Join Date
    Mar 2004
    Location
    Spring, TX
    Posts
    1,374

    Default Re: Conditional Formatting - Based on other cell Value/forma

    I think this will work. Here is your B1 conditional format screen (They MUST be in this order)

    Condition 1:
    Cell Value Is: equal to: ="Home" --> format grey
    Condition 2:
    Cell Value Is: equal to: ="End" --> format grey
    Condition 3:
    Cell Value is: greater than: =$A1 --> format red

    Copy down

    Conditional format for C1 is:

    Condition 1:
    Formula Is: =$B1="Home" -->format grey
    Condition 2:
    Formula Is: =$B1="End" --> format grey
    Condition 3:
    Formula Is: =$B1>$A1 --> format red

    Copy down and over to AO

    Let me know...

  10. #10
    MrExcel MVP, Administrator Von Pookie's Avatar
    Join Date
    Feb 2002
    Location
    The act or process of locating.
    Posts
    13,686

    Default Re: Conditional Formatting - Based on other cell Value/forma

    Quote Originally Posted by yee388
    Condition 1:
    Formula Is: =$B1="Home" -->format grey
    Condition 2:
    Formula Is: =$B1="End" --> format grey
    Condition 3:
    Formula Is: =$B1>$A1 --> format red

    Copy down and over to AO

    Let me know...
    Only problem is you left out the A=B condition
    Kristy

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com