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.