0

I am using working code, but this code has one drawback. The problem is that the gradient fill color is snapped to Select / Selection.

If dic.Exists(arr(1, n)) Then Cells(87, n + 1).Select

The macro highlights the required cells + the cell that was selected with the mouse. What / how should I change to select only the necessary cells (by condition)?

Sub G()
Dim dic As New Dictionary
Dim x, arr, iNum&, n&
Dim z As Range

Set z = [Q10]
iNum = Range("A85").Value2

Set wb = ThisWorkbook:
s1 = wb.Sheets("1").Range("C87").Value2

    Windows("2.xlsx").Activate
    n = Cells(Rows.Count, 48).End(xlUp).Row
    arr = Range("A2:CD" & n).Value2
    If Not IsArray(arr) Then Err.Raise xlErrNA
    For n = 1 To UBound(arr, 1)
        If arr(n, 77) = 1 Then
            If arr(n, 37) = iNum And arr(n, 3) = s1 Then x = dic(arr(n, 73))
        End If
    Next n
ThisWorkbook.Activate
n = Cells(20, Columns.Count).End(xlToLeft).Column
arr = Cells(20, 2).Resize(1, n).Value2

    z.Activate
    For n = 1 To UBound(arr, 2)
        If dic.Exists(arr(1, n)) Then Cells(87, n + 1).Select
        
    With Selection.Interior
        .Pattern = xlPatternLinearGradient
        .Gradient.Degree = 0
        .Gradient.ColorStops.Clear
    End With
    With Selection.Interior.Gradient.ColorStops.Add(0)
        .Color = 65535
        .TintAndShade = 0
    End With
    With Selection.Interior.Gradient.ColorStops.Add(1)
        .Color = 10498160
        .TintAndShade = 0
    End With
        
        
    Next n

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Peresmeh Ivan
  • 1
  • 1
  • 1
    [Avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – BigBen Jun 08 '21 at 13:23
  • Is there a reason you are not using conditional formatting instead of VBA? – cybernetic.nomad Jun 08 '21 at 14:53
  • @cybernetic.nomad 1) Conditional formatting loads the table (old computer in the office) 2) filling with a gradient color is applied based on certain conditions (determined by two Excel books). Not sure if this can be set through conditional formatting – Peresmeh Ivan Jun 08 '21 at 15:48

0 Answers0