+ Reply to Thread
Results 1 to 10 of 10

Change background color on value of 1 cell in a row

  1. #1
    Registered User
    Join Date
    08-20-2010
    Location
    Gelderland,Nederland
    MS-Off Ver
    Excel 2003
    Posts
    2

    Change background color on value of 1 cell in a row

    okay here's a tough one.

    when 1 cell in a row drops below 0 either -1 or -198 or something like that the whole row must turn red now I've tried using conditional formatting but then with a value above 0 the row also turns red.

    Yours sincirely,

    Roel

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Change background color on value of 1 cell in a row

    Hello Roel, welcome to the forum

    Highlight the cells you want to format, then use conditional formatting and select "Formula Is". If you want the row to turn red when the value in column B is below 0, enter a formula like

    =$B1<0

    (this assumes you are formatting row 1. If not, adjust the number). Leave the $ sign in front of the column letter, but don't enter a $ sign in front of the row number. Now choose your format.

    You can copy the row and paste special - formats to other rows.

    If you want to change the color of the row if any number in the row is negative, then use a formula like

    =MIN($A1:$G1)<0

    Adjust columns to suit.

    cheers
    Last edited by teylyn; 08-20-2010 at 04:18 AM.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Change background color on value of 1 cell in a row

    Hi and welcome to the board

    select your row - select Format conditional formatting - select "formula is" enter =your_cell_number<0 and choose the formatting

    PS Forget to add absolute to col ref :-(
    Last edited by arthurbr; 08-20-2010 at 05:05 AM.

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,323

    Re: Change background color on value of 1 cell in a row

    assuming you're checking cell A2 in row 2, use Conditional Formatting | Formula is with the formula:

    =$A2<0 and the background colour red. Copy this formatting to all other cells that you want to affect.

    Regards

  5. #5
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Change background color on value of 1 cell in a row

    TMShucks, how is that different to the suggestion I already posted above in my first recommendation? Just using a different cell reference, i.e. A1 instead of B2?

    After looking at the question again, I interpret
    when 1 cell in a row drops below 0
    to mean that if any cell in the row is less than 0, the whole row should change color.

    My second suggestion in my first comment does this.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,323

    Re: Change background color on value of 1 cell in a row

    @teylyn

    how is that different to the suggestion I already posted above
    Short answer: not different at all.

    Long answer: I guess I saw this, picked it up and worked on it in parallel with you ... you were just a lot quicker in responding. I hadn't taken the wider interpretation so, if that's what the OP wants, your solution is far better.

    Just the way it works. Should I/we wait to see if you come up with a solution before answering?

    In my experience, when someone asks a question, the number of responses can range between none and lots. And when there are lots, some of them will be the same or similar, as in this case. No big deal.

    Kind regards

    Regards

  7. #7
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Change background color on value of 1 cell in a row

    OK, with only 6 minutes difference, this can probably be categorised as simultaneous answering. I didn't realise that your answer was that (time) close to mine. No prob.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,323

    Re: Change background color on value of 1 cell in a row

    @teylyn

    I generally only look at posts that have had no replies. If you, or any of the other very experienced contributors are already in a thread, there is little chance that I will be able to add to it. When I first opened this post, there had been no responses. Depending on the complexity or personal interest, I will take the time to answer. I almost always work through my answer in a live workbook, even in relatively simple cases, to avoid making mistakes ... so it might take me a little longer than others. Even then, I can still screw up ... but I try not to :-)

    We're way off topic now so I guess we should draw a line. Good to talk.

  9. #9
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Change background color on value of 1 cell in a row

    @TMShucks
    If you, or any of the other very experienced contributors are already in a thread, there is little chance that I will be able to add to it.
    I wouldn't be so sure about that.

  10. #10
    Registered User
    Join Date
    08-20-2010
    Location
    Gelderland,Nederland
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Change background color on value of 1 cell in a row

    @teylyn
    Okay that worked, your help is very mutch appreciated.
    You sure know alot about excel.
    or is this as we say it a novice skill.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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