Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member
Former Member replied

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

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question