VBA: Colorir células num Range, baseado em valores definidos pelo utilizador

A idéia é de que toda vez que alterarmos os dados no intervalo B2:G2, as células do intervalo B4:G30 fiquem destacadas. 

 

  

 

 

E agora, o código VBA, que associamos ao botão “Destaca”:

 

Sub Color_Cells_In_Range()

    Dim FirstAddress As String
    Dim MySearch As Variant
    Dim Rng As Range
    Dim Rng1 As Long
    Dim Rng2 As Long
    Dim Rng3 As Long
    Dim Rng4 As Long
    Dim Rng5 As Long
    Dim Rng6 As Long
   
    Dim I As Long

Rng1 = Range(“B2”).Value
Rng2 = Range(“C2”).Value
Rng3 = Range(“D2”).Value
Rng4 = Range(“E2”).Value
Rng5 = Range(“F2”).Value
Rng6 = Range(“G2”).Value

    MySearch = Array(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6)
   
    With Sheets(1).Range(“B4:G43”)

        .Interior.ColorIndex = xlColorIndexNone

        For I = LBound(MySearch) To UBound(MySearch)

            Set Rng = .Find(What:=MySearch(I), _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)

            If Not Rng Is Nothing Then
                FirstAddress = Rng.Address
                Do
                    Rng.Interior.ColorIndex = 3
                    Set Rng = .FindNext(Rng)
                Loop While Not Rng Is Nothing And Rng.Address <> FirstAddress
            End If
        Next I
    End With
End Sub