+ Reply to Thread
Results 1 to 10 of 10

Highlight only one Cell in the entire row based on conditions

  1. #1
    Registered User
    Join Date
    05-29-2005
    MS-Off Ver
    2013
    Posts
    36

    Highlight only one Cell in the entire row based on conditions

    Hi

    I seem to be kind of stuck since the last 15 odd hours trying to figure a solution out for my problem and would really appreciate if someone could help.

    I have a timeline in row 1
    columns B:S have some data

    In the attached excel file you will notice that there are certain cells which have been highlighted in Red which has been done manually which is not a concern. and that's perfect. You will also notice that certain cells are highlighted in blue which are done manually I need these blue cells to highlight automatically

    What I need the macro / Solution to do

    Find first red cell and then based on the value of the red cell it needs to check the next column for

    if red cell is a negative number then look for the first lower value and highlight that cell blue
    if red cell is a positive number then look for the first higher value and highlight that cell blue

    I have over 100,000 rows of such data and don't think its possible to do all that manually. I would really really appreciate some help...

    smallfile.xlsm

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Highlight only one Cell in the entire row based on conditions

    Unable understand the logic

    Why Column-E is not marked with any blue color?
    Why H3 alone marked in blue color and based on what?


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Valued Forum Contributor
    Join Date
    03-17-2012
    Location
    Warsaw, Poland
    MS-Off Ver
    2007/2010
    Posts
    555

    Re: Highlight only one Cell in the entire row based on conditions

    @ sixthsense

    because the values in column e are not smaller than the cells marked with red. Why some cells are marked with blue is completely irrelevant. OP has prolly some logic behind that.

    What bothers me is that you didn't say that almost every Cell is filled with red (255) and their appearance is just conditional formating at play.

    Please Login or Register  to view this content.
    This code works, assuming that the colors are NON-Conditional formated
    If you think that my answer was helpful, please click on the "Add to this user's Reputation" button.

  4. #4
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,767

    Re: Highlight only one Cell in the entire row based on conditions

    @ Bishonen,

    Thanks for the brief and lets see whether your provided solution fulfills the OP's requirement

  5. #5
    Registered User
    Join Date
    05-29-2005
    MS-Off Ver
    2013
    Posts
    36

    Re: Highlight only one Cell in the entire row based on conditions

    Hi Bishonen,

    Wow this is great...the macro works perfect for the negative numbers however there is a small challenge... the positive numbers dont seem to be working well. Would be super if there is a work around...I have attached the file for your reference..

    And once again I truly appreciate the time you have spent so far on the file... u rock
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-29-2005
    MS-Off Ver
    2013
    Posts
    36

    Re: Highlight only one Cell in the entire row based on conditions

    Quote Originally Posted by :) Sixthsense :) View Post
    Unable understand the logic

    Why Column-E is not marked with any blue color?
    Why H3 alone marked in blue color and based on what?
    Why Column-E is not marked with any blue color?

    We will need to look it row wise..
    Example Cell D2 is highlighted in red and the value -1.59 now since -1.59 is a negative number it needs to find the next lowest number on the right side and highlight that cell blue. E2 is not lower than -1.59 so it moves to the next, cell F2 (-1.96) is lower than -1.52 so it stop there and highlights the cell Blue

    Why H3 alone marked in blue color and based on what?[/QUOTE]
    F3 is highlighted in red and the value is -1.87
    G3 has -1.87 which is not lower than our original value (F3 -1.87) so it moves to the next cell
    H3 has -2.64 which is lower than our original value (F3 -1.87) here the criteria is met so it stop there and highlights the cell Blue

    So the above is dealing with negative numbers


    Now if the number is positive

    O6 is highlighted in Red and has the value 1.02 Since this is a positive number now it needs to find the next higher number
    P6 has 1.3 which is higher than our original number (O6 1.02)so it stop there and highlights the cell Blue.


    As soon as the criteria is met it needs to stop highlighting any other cells in that row...

  7. #7
    Registered User
    Join Date
    05-29-2005
    MS-Off Ver
    2013
    Posts
    36

    Re: Highlight only one Cell in the entire row based on conditions

    Quote Originally Posted by ascool_asice View Post
    Hi Bishonen,

    Wow this is great...the macro works perfect for the negative numbers however there is a small challenge... the positive numbers dont seem to be working well. Would be super if there is a work around...I have attached the file for your reference..

    And once again I truly appreciate the time you have spent so far on the file... u rock

    Any takers on this? The files almost done... its not handling the +ve numbers

  8. #8
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Highlight only one Cell in the entire row based on conditions

    Maybe:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    05-29-2005
    MS-Off Ver
    2013
    Posts
    36

    Re: Highlight only one Cell in the entire row based on conditions

    Quote Originally Posted by JOHN H. DAVIS View Post
    Maybe:

    Please Login or Register  to view this content.

    Hi John,

    Let me start by thanking you for taking the time out to help me out...

    The file works like a miracle... you are a genius... Thanks a ton... I can finally party tonight U rock

  10. #10
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: Highlight only one Cell in the entire row based on conditions

    You're welcome. Glad to help out and thanks for the feedback.

+ 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. [SOLVED] Highlight entire row based of multiple if conditions.
    By nhi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-13-2013, 05:59 PM
  2. Replies: 2
    Last Post: 06-05-2013, 11:03 AM
  3. [SOLVED] Using conditional formatting to highlight an entire row based on one cell
    By eizanendoso in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2012, 09:59 AM
  4. Highlight entire row based on cell value
    By clwrs in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 08-14-2009, 06:51 PM
  5. How Do I Highlight a Cell Based on Conditions of Another?
    By Ocean Zhang in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-05-2009, 01:21 PM

Tags for this Thread

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