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.
ee-samp2.xls
LeeStinkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

oleggoldCommented:
"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"
0
LeeStinkAuthor Commented:
I dont need to shade the whole row, just the row beginning with column C..

thanks for the reponse.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

oleggoldCommented:
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].

"
0
LeeStinkAuthor Commented:
it has to be a macro...  it needs to be automated.
0
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..??
0
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.
0
Saurabh Singh TeotiaCommented:
Basis of your example, You can use the following code and it will do what you are looking for...
Saurabh...

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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

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.