Results 1 to 10 of 10

Highlight Row Based on Specified Columns

This is a discussion on Highlight Row Based on Specified Columns within the Excel Questions forums, part of the Question Forums category; I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. ...

  1. #1
    Board Regular
    Join Date
    Dec 2005
    Location
    San Francisco
    Posts
    361

    Question Highlight Row Based on Specified Columns

    I would like to have Excel 2003 be able to highlight the row if there is data in certain columns. Specifically if there is a number greater than zero.

    For example the following could work:

    [CODE][=OR(LEN(H9),LEN(I9),LEN(K9),LEN(Q9),LEN(R9),LEN(S9),LEN(W9),LEN(X9),LEN(Y9),LEN(AB9),LEN(AD9))/CODE]

    However, there are times when I need to add a column/criteria. Thus I would have to go into conditional format and redo all the rows in the worksheet. Is there a smarter way to do this?

    I was hoping to provide a list of cell address from the header or better than that look for specific numbers in the header row and if there is a number greater than zero, then highlight the entire row.

    There is no specific pattern as to which columns would trigger the requirement.

    Also how do you keep the ranges from moving when copying across and down. Sometimes I run into that issue. Maybe highlight the entire row and then enter the conditional format?
    The recipe for perfect toast: Burnt minus 1 minute

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    800

    Default Re: Highlight Row Based on Specified Columns

    I'm not clear as to what you want, but I'll offer the following in any event.

    To save having to amend/update your conditional formatting (CF) rules whenever you add further columns, you could use a "helper" column directly in the spreadsheet that provides the flag to the CF rule as to whether or not the row should be highlighted. That is, instead of incorporating your trigger formula in the CF rule itself, have it in a cell in the spreadsheet (it might need to be an =IF formula) to return TRUE or FALSE, Yes or No, or whatever, regarding the data in the criteria columns you wish to count, with the CF rule simply checking this result. It is easy to copy your formula and any CF format to new rows without having to amend them.
    BigC
    Using Excel 2007 / 2010

  3. #3
    Board Regular
    Join Date
    Dec 2005
    Location
    San Francisco
    Posts
    361

    Default Re: Highlight Row Based on Specified Columns

    BigC, yes that is a good suggestion that could work. However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.

    Any ideas?
    The recipe for perfect toast: Burnt minus 1 minute

  4. #4
    Board Regular
    Join Date
    Dec 2005
    Location
    San Francisco
    Posts
    361

    Default Re: Highlight Row Based on Specified Columns

    BigC, yes that is a good suggestion that could work. However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.

    Any ideas?
    The recipe for perfect toast: Burnt minus 1 minute

  5. #5
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    800

    Default Re: Highlight Row Based on Specified Columns

    Quote Originally Posted by dwgnome View Post
    However I would prefer a more robust way of executing the CF that seems to work as shown in my OP, but need more flexibility.
    That depends on what you mean by robust. My original suggestion is far easier to maintain than amending the CF each time you add a column, and is more transparent (you can see it working directly in the spreadsheet) so I beleive that is more "robust" - but that's the beaut thing about Excel, there are many ways to skin the proverbial cat!

    What about this idea (which is a different take on my original one)?
    1. Using row 9 as the starting point (as per your example), click on any cell in row 9.
    2. In the Define Name dialogue box (I currently use Excel 2007 so am trying to remember the 2003 commands!), create a name (e.g. "CF_Trigger") with the following formula as the "Refers to:" =OR(LEN($H9),LEN($I9),LEN($K9),LEN($Q9),LEN($R9),LEN($S9),LEN($W9),LEN($X9),LEN($Y9),LEN($AB9),LEN($AD9))
    3. Note that it is critical that:
      1. the row number in the formula is the same as the cell you selected when defining the name. This creates a dynamic/relative defined name that returns a result (True or False) based on the values in the row to which it is applied. (That's not a good explanation, but I can't think how else to say it!)
      2. You include the $ before the column reference to lock in the criteria columns

    4. Select the range to which you wish to apply CF, and in the CF rule, just enter "=CF_Trigger" and apply the required format.


    Result:
    1. The Defined Name will return a separate True or False result for each row to which CF is applied (though you can't actually see this - it's in the black box!)
    2. The CF rule will use the result from the Defined Name (for each row) to apply (or not) the CF to each row
    3. When you add a new column, you only need to amend the "Refers To" formula in the Defined Name (one simple edit to add LEN(XXn) as another argument to the existing OR function) in order to apply the change to the whole spreadsheet.


    As far as I can see, if you don't want to fiddle with the CF each time and don't want to utilise VBA (far more complex), what I've offered are your two options (i.e. formula in spreadsheet to return T/F result to the CF rule, or a formula in a Defined Name to do the same thing)
    BigC
    Using Excel 2007 / 2010

  6. #6
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,374

    Default Re: Highlight Row Based on Specified Columns

    How about this in the sheet module
    Code:
    Sub worksheet_change(ByVal target As Range)
    Dim lc As Integer, lr As Long, x As Integer
    lc = Cells(1, Columns.Count).End(xlToLeft).Column 'change to suit
    lr = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
    For r = lr To 1 Step -1
        x = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, lc)))
        If x > 0 Then
            Rows(r).Interior.ColorIndex = 3 'change to suit 3 =Red
         End If
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  7. #7
    Board Regular
    Join Date
    Dec 2005
    Location
    San Francisco
    Posts
    361

    Default Re: Highlight Row Based on Specified Columns

    Thanks BigC, your solution is clever, but a bit complex for me. Perhaps if you had a template or example spreadsheet to go by, I may be able to follow.

    Michael M, I tried your solution, but nothing happened when I copied your script into a new module in ThisSheet and saved it. Maybe I am doing something wrong as I am a novice when it comes to VBA.
    The recipe for perfect toast: Burnt minus 1 minute

  8. #8
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    9,374

    Default Re: Highlight Row Based on Specified Columns

    The code has to go in a sheet module not a Standard (new) module
    So, if you want it to work in Sheet1, that's the module you put it in.
    Also remember, If you have headers in the rows and columns, you will need to modify line 6 in the code


    Code:
    Sub worksheet_change(ByVal target As Range)
    Dim lc As Integer, lr As Long, x As Integer
    lc = Cells(1, Columns.Count).End(xlToLeft).Column 'change to suit
    lr = Cells(Rows.Count, "A").End(xlUp).Row 'change to suit
    For r = lr To 1 Step -1
        x = WorksheetFunction.CountA(Range(Cells(r, 1), Cells(r, lc)))'checks from col "A" to last col and row 1 to last row....change as required
        If x > 0 Then
            Rows(r).Interior.ColorIndex = 3 'change to suit 3 =Red
         End If
    Next r
    End Sub
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    work 2003, Home 2007

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  9. #9
    Board Regular
    Join Date
    Dec 2005
    Location
    San Francisco
    Posts
    361

    Default Re: Highlight Row Based on Specified Columns

    Okay, I do have headers and on the left a column of id numbers. I want all cells within the table and row to be highlighted based on a list. Does your script look for a list, compare then if true changes the cell background to a color?

    I don't know how to edit the code as I do not understand VBA.


    TANK NO. Nitric Acid Sulfuric Acid Hydrochloric Acid Hydroflouric Acid
    CAS 7697-37-2 7664-93-9 7647-01-0 7664-39-3
    specific density 1.40 1.84 1.16 1.18
    density (lb/gal) 11.68 15.35 9.67 9.84
    32 25% 4%
    33
    35

    The first row to be highlighted is Tank 32, with light blue because there is a number in the column for sulfuric acid and in my real spreadsheet that column is I and the Tank 32 row is row 9.

    Thanks again.
    The recipe for perfect toast: Burnt minus 1 minute

  10. #10
    Board Regular
    Join Date
    Aug 2002
    Location
    Perth, Western Australia
    Posts
    800

    Default Re: Highlight Row Based on Specified Columns

    Quote Originally Posted by dwgnome View Post
    Thanks BigC, your solution is clever, but a bit complex for me.
    Complex??

    All you need do are two fairly simple steps:

    1. create a Defined Name (say "CF_Trigger" as described), which in Excel 2003 is achieved via Insert / Name / Define, and
    2. amend your existing CF rule to replace the long =OR(...) formula (which is now assigned to the Defined Name) with "=CF_Trigger"


    All this does is create an intermediate step in the CF process, but that step provides the flexibility and simplicity (to edit as your database expands) you desire.
    BigC
    Using Excel 2007 / 2010

Tags for this Thread

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