Replace with offset value and highlight-CodePudding

Is there any way to modify this code to also have the replaced value highlighted?

The last line replaces the found value with an offset value (usually just to correct grammar), i'm also trying to get it to color the cell background in green.

Sub test_Dam()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim f As Range
Dim i As Long, col1 As Long, ini As Long
 
'set worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Brands")
 
Set f = sh1.Cells.Find("Brand", , xlValues, xlPart, , , False)
If Not f Is Nothing Then
col1 = f.Column
ini = f.Row   2

For i = ini To sh1.Cells(Rows.Count, col1).End(3).Row
Set f = sh2.Cells.Find(sh1.Cells(i, col1).Value, , xlFormulas, xlPart, xlByRows, xlNext, False)
If Not f Is Nothing Then
sh1.Cells(i, col1).Value = f.Offset(0, 0).Value ' trying to add this to highlight

End If
Next
End If

End Sub

CodePudding user response：

This is how to colour a cell:

Option Explicit
Sub test_Dam()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim F As Range
    Dim I As Long, col1 As Long, ini As Long
     
    'set worksheet
    Set sh1 = Sheets("Sheet1")
    Set sh2 = Sheets("Brands")
     
    Set F = sh1.Cells.Find("Brand", , xlValues, xlPart, , , False)
    If Not F Is Nothing Then
        col1 = F.Column
        ini = F.Row   2
        
        For I = ini To sh1.Cells(Rows.Count, col1).End(3).Row
            Set F = sh2.Cells.Find(sh1.Cells(I, col1).Value, _
                , xlFormulas, xlPart, xlByRows, xlNext, False)
            If Not F Is Nothing Then
                sh1.Cells(I, col1).Value = F.Offset(0, 0).Value ' trying to add this to highlight
                sh1.Cells(I, col1).Interior.ColorIndex = 6
            End If
        Next I
    End If

End Sub

HERE IS A DETAIL OF COLOR AND COLORINDEX