If value in cell equals this, then other cells equal this

[trubio77]

Hello,

Need some assistance

we prefer VBA macro for the following

I've attached a sample

if B4 = B9:B14, then C4:J4 = C9:J9

Users will enter data in B4 in order to autofill C4:J4

thank you

[6StringJazzer]

First your description is a little cloudy. Do you mean this:

When cell B4 is changed, see if that value is contained in the range B9:B14. If so, then copy the contents of C:J in the row where the value was found into C4:J4

.


Here is a macro. But this is trivial to do with formulas. Why do you prefer a macro?

Note that your formatting on row 4 prevents the proper display of the data.

This will give an error if the value is not found. You can eliminate the possibility of error by using Data Validation with a list in B4 and use a named formula for the list.

You must put this code into the module for Sheet1:

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim Cell As Range
   Dim Found As Range
   
   For Each Cell In Target
      If Cell.Address = [B4].Address Then
         Set Found = [B9:B14].Find(what:=Cell.Value, Lookat:=xlWhole)
         If Found Is Nothing Then
            MsgBox "Value " & [B4] & " was not found."
            [C4:J4].ClearContents
         Else
            [C4:J4].Value = Range(Cells(Found.Row, "C"), Cells(Found.Row, "J")).Value
         End If
      End If
   
   Next Cell

End Sub

[trubio77]

6stringjazzer,

this worked

thank you