IF statement with multiple parameters and overwriting cell values in separate sheet

[ave10]

Hello,

I have a scenario/idea I would like to try but honestly, I am not good enough with VBA to get it done in a reasonable time. I'm aware this isn't a "Code for me" site but I thought I'd describe the scenario as detailed as I can and see if someone can help me or give me an idea of how I can translate it into VBA.


In a workbook I have, users either manually enter an account code or select one from a list and the account codes are placed in column C (C7:C446) in a sheet called "JE". The account codes look like this #######-###-##-######. In column D, there is a formula that captures the last 6 digits of the account code.

So, I'd like to write: In a sheet titled "JE", IF the F column cells (F7:F446) interior color is red AND the value in the D column cell (of the same row) equals a value listed in sheet "required_refs", put the value in the D cell of sheet "JE" inside cell D1 in sheet "reference".

example: if cell F25 has an interior color of red, AND the value of D25 matches any of the values listed in column A of sheet "required_refs", upon double clicking the F25, put the value of D25 on sheet "JE", and put it in cell D1 on sheet "references"

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim project As Range: Set project = Range("D7:D446")
    
    For Each cell In project
        If project.Value = Worksheets("required_refs").Range("A:A").Value Then
        Call gotoRef_ 'macro that simply selects/navigates to the required_ref sheet
        'here I would like to replace the value in cell D1 of sheet "references" with the value of the active row's column D value
End Sub

Any help or suggestions would be much appreciate. Thanks so much!

 

[ave10]

Iv'e updated my code a bit:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim project As Range: Set project = Range("D7:D446")
    Dim param As Range:    Set param = Worksheets("references").Range("D1").Value
    
    
    For Each cell In project
        If project.Value = Worksheets("required_refs").Range("A:A").Value Then
        Call gotoRef_ 'macro that simply selects/navigates to the required_ref sheet
        project.Value = param
        End If

End Sub