+ Reply to Thread
Results 1 to 9 of 9

Highlight 1 cell based on value from other cells

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Highlight 1 cell based on value from other cells

    I have a spread sheet with dollar values from cell L3 to L148 skipping every second cell(L3,L5,L6 etc) with cell L154 being the total of all the cell. If any of these cell doesn't have a dollar value the cell is highlighted. Because I can't see the entire page at once if I only look at the total I might not see if one cell is blank and highlighted. Is there a way to format so that if any of the cells from L3 to L148 don't have a dollar value cell L154 with the totals would be highlighted. Just to note that even though these cells might not have a dollar value they still contain a formula.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Highlight 1 cell based on value from other cells

    what do you currently make a cell that has no dollar value in it? is it "" or something else what is the conditional format for those cells?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Highlight 1 cell based on value from other cells

    The cells are currently formatted to highlight if value is less than 1. So basically if any of the cells are highlighted I would want the cell that contains the total to also be highlited.

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Highlight 1 cell based on value from other cells

    what does the formula in the cell return is it 0 or blank or something else

  5. #5
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Highlight 1 cell based on value from other cells

    If the cell is highlighted is see $_

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Highlight 1 cell based on value from other cells

    thats just formatting what is the formula in cells l3,l5 etc i need to know what to test for
    but if the cells already change colour on<1 then try conditional format total cell as
    =SUMPRODUCT(--(MOD(ROW($L$3:$L$148),2)=1),--($L$3:$L$148<1))>0

  7. #7
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Highlight 1 cell based on value from other cells

    There are 2 different formulas. one formula is =(C12*E12*8*O1)+(C12*G12*O1)the other is =E42*G42. They all reference the row below were the formula is located. The column stay the same.

    There are also a couple that have no formula at all. Just $_ until I manually input an amount.
    Last edited by freeon; 09-10-2013 at 01:14 PM.

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Highlight 1 cell based on value from other cells

    then that should work fine

  9. #9
    Registered User
    Join Date
    01-15-2013
    Location
    Moncton
    MS-Off Ver
    Excel 2016
    Posts
    95

    Re: Highlight 1 cell based on value from other cells

    I tried and it seems to work beautifully. I still have some testing to do as this is still a build in progress but it appears it will do the trick. I want to do the similar things in other work sheets and I should be able to modify your code to work other sheets. Thanks so much.

+ 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: 4
    Last Post: 01-03-2013, 12:25 AM
  2. Highlight or colour a cell or cells based on cells in another range.
    By baffld in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-30-2012, 08:41 PM
  3. Replies: 3
    Last Post: 08-28-2009, 11:16 AM
  4. Macro to highlight cells based on content
    By JimDerDog in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-01-2006, 11:55 AM
  5. Replies: 5
    Last Post: 05-27-2005, 02:05 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