Creating a macro to simulate conditional formats on a row by row basis.

QuestionEdit

I'm fluent in PHP and JS, I do not know VB so this is proving to be a challenge.

Here's what I'm trying to do: create a sheet that I can use to track the current status of work orders. It has 7 columns, 43 rows. Col A uses dropdown lists (via data validation) to apply a background color to the cells of the row which it is located. I intend for every data row (3 thru 42) to have it's own dropdown list which changes the status color for the entire entry (row). The problem is that if I set the Range to "A3:G43", then any row in the range whose menu is not yet populated, changes color to whatever is selected on the current row. The behavior becomes more erratic if I "status" an entry on a lower row, and then do the same to a higher row. What I basically want is to figure out a way to have the macro apply to only the specific line that is selected at the time. So if I click on a item on my dropdown menu on A36, it only changes the color of A36:G36, not A36:G42. Is there a way to add a variable to the "row" number in the range and apply the row number of the currently selected row in it's place on the fly? Or is there a better way to do this? Here's the code that I'm using (I pulled it off a website):

Private Sub Worksheet_Change(ByVal Target As Range) Dim Num As Long Dim rng As Range Dim vRngInput As Variant Set vRngInput = Range("A3:G43") If vRngInput Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False For Each rng In vRngInput Select Case UCase(rng.Value) Case Is = "COMPLETE": Num = 48 Case Is = "WORKABLE": Num = 35 Case Is = "H/F EPD": Num = 37 Case Is = "H/F PARTS": Num = 28 Case Is = "H/F SEQUENCE": Num = 27 Case Is = "OTHER": Num = 24 End Select rng.Interior.ColorIndex = Num Next rng endit: Application.EnableEvents = True End Sub

AnswerEdit

If you want the code to impact the color index of the active row, then you can probably get rid of of the For-Next loop. That is checking the value of each cell in A3"G43 every time something in the worksheet changes. The following will adjust the interior color index of the active row only. I doubt that it will be perfect, but, it should give you some ideas.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Num As Long

Select Case UCase(ActiveCell.Value)

   Case Is = "COMPLETE": Num = 48
   Case Is = "WORKABLE": Num = 35
   Case Is = "H/F EPD": Num = 37
   Case Is = "H/F PARTS": Num = 28
   Case Is = "H/F SEQUENCE": Num = 27
   Case Is = "OTHER": Num = 24

End Select

ActiveCell.EntireRow.Interior.ColorIndex = Num

End Sub