help with vba

[p4nny]

Hi

The problem here is I'm trying to run 2 separate pieces of code independently within the same sub. The first part of the code changes the row colour when range AA = "closed"

The second part enters a message in column D when C = "Y" or "N"


Any help is appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)


On Error Resume Next


Dim i As Long, r1 As Range, r2 As Range


   For i = 2 To 1000
      Set r1 = Range("aa" & i)
      Set r2 = Range("A" & i & ":ab" & i)
      If r1 = "closed" Then r2.Interior.ColorIndex = 15




      
      Exit Sub
      
      
   Next i




    
    If Intersect(Target, Range("c2:c100000")) Is Nothing Then
        Exit Sub
    End If


    Application.EnableEvents = False


    If Target.Value = "Y" Then
        Target.Offset(, 1).Value = "Key in the dependency ID"
    Else
        Target.Offset(, 1).Value = "N/A"
    End If


    Application.EnableEvents = True


End Sub

 

[wideboydixon]

Hi,

I think you're assuming that I have a level of knowledge about your workbook. Could you describe exactly what you're trying to do? Is this something related to changing a value in Column C since that's what the original code was intended to do.

WBD

 

[p4nny]

Hi

thanks for your reply.

When testRange = Y, I would like to find the value located in offset(,-1) in the sheet called Dependencies.

Hope that makes sense

I think I'm struggling with writing the Find function in the correct format. I've googled it too.

Many thanks