Copy background of given cell and paste in range

[R23_Wave]

Hello everyone,

i am trying to create a worksheet that will find the value input into column "D" and turn the other cells in that row the same color for example:
before input

after input

currently I have the below code and don't know where to go from here

Private Sub worksheet_Change(ByVal captain As Range)

Dim rangeToChange As Range
Dim colorLocation As Range

Set rangeToChange = Range("F5:F305")
'this makes sure the changed cell is with in the captain column
If Not Intersect(captain, rangeToChange) Is Nothing Then
    'this makes sure the cell is not blank before doing something
    If captain <> " " Then
        'this is if the cell value can't be found in the list then msgbox letting us know
        If Range("H5:Q5").Find(Range(captain.Address)) Is Nothing Then
            MsgBox "Value not found"
        Else
        'this is to get the address of the captain in the table
            Set colorLocation = Range(Range("H5:Q5").Find(Range(captain.Address)).Address)
            
        'insert code to copy captains cell H5:Q5 formatting and paste it
        'just formatting into all cells A-F in table for given row   
            
        End If
    End If
End If
    
End Sub

 

[mackc557]

This sounds like what I use Conditional Formatting for.

Select first row first column of the full range you want to make these apply to.
Select conditional formatting, manage rules, new rule.
Select Use Formula
This formula will look like an Excel cell formula, ie. =IF($F5 = $H$5) (Based on your sample)
Set your formatting
Click Ok
Change the range from =$A$5 to =$A$5:$F$9 (Based on your sample)
Press Enter
Click Apply

In the above steps, you can change the $H$5 to a name of a cell.

 

[Fluff]

How about

            Set colorLocation = Range(Range("H5:Q5").Find(Range(captain.Address)).Address)
            captain.Offset(, -5).Resize(, 6).Interior.Color = colorLocation.Interior.Color