Skip to Content
avatar image
Former Member

VB Code Examples for Highlighting a particular total row (BEX)

I need to highlight one of my results rows (not all) in a different color. I'm thinking that I should be able to create a formula that states if Column G = "RESULT" then highlight the entire ROW.

My problem is that I don't know VB. If anyone can point me to a good site for VB examples/web sites?

Thanks

JP

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Apr 20, 2006 at 05:58 PM

    Hi JP,

    Let's say that your query's local query ID is SAPBEXq0001 (look at Information tab of Properties dialog).

    Then, the named range for the query result table will be "SAPBEXqueries!SAPBEXq0001".

    To know the first and last row in the result table:

    Dim rng as Range

    Set rng = Range("SAPBEXqueries!SAPBEXq0001")

    firstRow = rng.Cells(1).Row

    lastRow = firstRow + rng.Rows.Count - 1

    To look at each entry in Column G:

    For i = firstRow to lastRow

    myVal = Range("G" & i)

    Next i

    To look at each entry in Column G and change colors based on what you find:

    For i = firstRow to lastRow

    myVal = Range("G" & i)

    If myVal > ??? then

    Rows(i).Interior.Color = vbYellow

    End If

    Next i

    If you only want to color the cells in the result table and not the entire row, then find first and last column similar to example for first and last row (use Column instead of Row) and for the color use:

    With Range(cells(i, firstCol), cells(i, lastCol))

    .Interior.Color = vbYellow

    End With

    You can get more colors by turning on the Macro recorder (in Excel use Tools >> Macro >> Record), change a color and see what you get. You notice that Excel will use ColorIndex instead of Color. They are different, but give the same result.

    - Pete

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Apr 20, 2006 at 06:15 PM
    Add comment
    10|10000 characters needed characters exceeded