VBA Conditional Formating

[AndreaM]

Hi,

I need assistance and haven't been able to figure it out. I am trying to do conditional formatting via VBA because I need the formula to do a lookup from a variable of a Cell but cannot use relative references. Basically I want to use Icon Sets using a formula when the value is > than and the >=. I would like the formula to do a lookup from Column A to match a value in Column A from another Sheet and then return the value from a cell in Column D from the lookup.

Sheet 1

	A	B	C
1	ADDSURE	800	350
2	FRUITONE	300	900
3	HHO	400	700

Sheet 2

	A	D	F
3	ADDSURE	850	350
7	FRUITONE	300	850
12	HHO	400	700

I would like All values in Column B and C to show an up Arrow or Down Arrow if the Values from Sheet 2 Column D and F are > than or = to the Values in Sheet 1.

Kind regards
Andrea

[offthelip]

something like this should work:

Sub formarr()
Dim inarr As Variant
With Worksheets("Sheet2")
    lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
    inarr = Range(.Cells(1, 1), .Cells(lastrow, 6))
End With
With Worksheets("Sheet1")
    lastrow1 = .Cells(Rows.Count, "A").End(xlUp).Row
    thisarr = Range(.Cells(1, 1), .Cells(lastrow1, 6))
End With


For i = 1 To lastrow1
  For j = 1 To lastrow
   If thisarr(i, 1) = inarr(j, 1) And thisarr(i, 1) <> "" Then
     With Cells(i, 2)
     .FormatConditions.AddIconSetCondition
     .FormatConditions(1).SetFirstPriority
    With .FormatConditions(1)
        .ReverseOrder = False
        .ShowIconOnly = False
        .IconSet = ActiveWorkbook.IconSets(xl3Arrows)
    End With
    With .FormatConditions(1).IconCriteria(2)
        .Type = xlConditionValueNumber
        .Value = 0
        .Operator = 7
    End With
        With .FormatConditions(1).IconCriteria(3)
        .Type = xlConditionValueNumber
        .Value = inarr(j, 4)
        .Operator = 7
    End With
   End With
   End If
  Next j
Next i
  
End Sub

[AndreaM]

Thank you so much for this. Worked like a charm.