excel 替换为偏移值并高亮显示

oxcyiej7 于 2023-01-21 发布在其他

关注(0)|答案(1)|浏览(122)

有没有办法修改这段代码，使替换的值也突出显示？
最后一行用一个偏移值替换了找到的值（通常只是为了纠正语法），我还尝试让它把单元格背景着色为绿色。

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

excel

来源：https://stackoverflow.com/questions/75188929/replace-with-offset-value-and-highlight

1条答案

按热度按时间

6bc51xsx1#

下面是如何给一个单元格着色：

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

赞(0）回复(0）举报 2023-01-21

我来回答

excel 替换为偏移值并高亮显示

1条答案

相关问题

热门标签

最新问答