Excel VBA: dynamically shade rows based on value of a cell

see attached.  I need the yellow rows to appear as shown.   the number of rows in the report can vary, so th emacro needs to be dynamic.
Who is Participating?
Saurabh Singh TeotiaConnect With a Mentor Commented:
Basis of your example, You can use the following code and it will do what you are looking for...

Sub highlight()
    Dim rng As Range, lcol As Long, lrow As Long, CELL As Range
    lcol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    lrow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    Set rng = Range("C6:C" & lrow)
    For Each CELL In rng
        If CELL.Value = "Target" Or CELL.Value = "Non-Target" Then
            Range("C" & CELL.Row & ":" & Cells(CELL.Row, lcol).Address).Interior.ColorIndex = 36
        End If
    Next CELL
    MsgBox "Done"
End Sub

Open in new window

"We use the =INDIRECT() function to get the value of column C for each row:
If the value is TRUE : =INDIRECT("C"&ROW())=TRUE
If the value is 1 : =INDIRECT("C"&ROW())=1"
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

LeeStinkAuthor Commented:
I dont need to shade the whole row, just the row beginning with column C..

thanks for the reponse.
sorry ,here"
Excel has a great built-in function to achieve this: Conditional Formatting (In the menu Format ยป Conditional formatting). The first time it might be a little difficult to find your way to do this but if you spend a little time with it you will soon realize it is powerful function.

In the following example if we want the rows to be colored blue when column C is true and orange if the value is 1, we can use the following formula's:
Select all the cells that need to be colored. Usually we color a cell based an its value and use the [Cell Value Is ] in the conditional formatting box. To format cells based on other cells you need the [Cell Formula Is].

LeeStinkAuthor Commented:
it has to be a macro...  it needs to be automated.
Saurabh Singh TeotiaCommented:
I dont see the rows shaded to yellow..What is the condition that is meet when you want to shade them..? also do you want to shade  data from C Column to AG Column only..??
LeeStinkAuthor Commented:
rows where the value of column C are "Target" or "Non-target" need to be shaded yellow, and all cells to the right of the cell need to also be shaded, so yes, the entire row, but not the cells to the left of column C.

Thank you.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.