Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Highlighting rows based on column value

    Hi,

    I want a macro that can highlight entire row based on a cell value.

    I am attaching a spreadsheet wherein i want to look for value "0" or "Null" in column B and if column B contains any of these value, all the rows similar to the name in column A gets highlighted in Red (or any color.)

    In my attached example column A has names and column B has codes.

    As column B has one "0" and the name against it is Rakesh, so I want all the rows to be highlighted that contains Rakesh in column A.

    Similarly, if column B contains "Empty", I want all the rows to be highlighted that contains Sam in column A.

    Any help of this is highly appreciated.

    Thanks in advance!!


    Regards,
    Ankit
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Is it OK to have an intermediate value related to the 0 and "Empty" ?

    If so, try the attached with conditional formatting.
    Attached Files Attached Files

  3. The Following User Says Thank You to kweaver For This Useful Post:

    ankitag85 (2012-07-22)

  4. #3
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    Conditionally formatting is fine but the data contains thousand of rows with different values in every column (not just sam and rakesh in column A). Therefore, it would be difficult to manage data with this conditional formatting and index formula.

    I guess, a macro would certainly help that could loop around the entire sheet, search for 0 and empty in column A and then highlight the rows based on column B value.

    Please advise.

  5. #4
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Also, there is a failure in the conditional formatting that, whenever multiple empty or 0 appears in column B against different values in Column A, it only highlights one entry (Rows based on ane value in column A) not all of them.

  6. #5
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Please anyone help on this.

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The cond formatting / intermediate can work. You can create a list of the matching names in (for example) col D with the formula [this column can be hidden]:
    =IF(OR(B2=0,B2="empty"),A2)

    Then use this list as a lookup. Select the range (eg A1:B13) and set the formula to cond formatting to:
    =ISNUMBER(MATCH($A2,$D$2:$D$13,0))

    and format as desired.

    This does NOT completely match what you have highlighted. You either forgot to format row 4 (sam,3) or there is more to the formatting rules than you indicated. "Sam, 3" should be highlighted since there is a "Sam, Empty" in another row...

    Steve
    PS formulas and cond formatting is much more efficient than code for a "live solution". A live solution with code, will become sluggish the more rows it must check and also disable the UNDO feature.

  8. The Following User Says Thank You to sdckapr For This Useful Post:

    ankitag85 (2012-07-22)

  9. #7
    Star Lounger
    Join Date
    Apr 2008
    Posts
    75
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks Steve...

    It's works fine...and now, I understand the logic...

    Gr8 help!!

Posting Permissions

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