ConditionalFormat Offset Arg1 in match function.

Hi all,

Syntax
expression.Match( Arg1, Arg2, Arg3 )

In code below I have one of three expressions just to make code easy to read.
Arg1=C42. After first loop Arg1 must be C60 etc etc.
How to change Arg1 in match function?

Sub CondFormat_AddTypeXlExpression()

Dim rng30   As range
Dim rng31   As range
Dim rng32   As range
'Dim rng32   As Variant
Dim i       As Long

Set rng30 = range("C42:C56")
Set rng31 = range("B1")
Set rng32 = range("C42")

For i = 1 To 8
    With rng30
        .FormatConditions.Delete
        .HorizontalAlignment = xlLeft
    End With
    
        rng30.Select
        selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
            "=AND(AND(rng31<>""Apples"";rng31<>""Oranges"";MATCH(rng32;Sheet5!$A$5:$A$20;0)))"      'My "rng32" variable doesn't work
            '"=AND(AND(rng31<>""Apples"";rng31<>""Oranges"";MATCH(C42;Sheet5!$A$5:$A$20;0)))"       'With this line it works, but only before the first loop.
        selection.FormatConditions(selection.FormatConditions.Count).SetFirstPriority
        With selection.FormatConditions(1).Font
            .color = RGB(255, 0, 0)
        End With
        With selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .color = RGB(0, 32, 96)
        End With
        selection.FormatConditions(1).StopIfTrue = False

    Set rng30 = rng30.Offset(18)
    Set rng31 = rng31.Offset(, 2)
    Set rng32 = rng32.Offset(18)
Next i
    
End Sub

Any help will be greatly appreciated.
Thanks!

How about

            "=AND(" & rng31.Address & "<>""Apples""," & rng31.Address & "<>""Oranges"",isnumber(MATCH(" & rng32.Address & ",Sheet5!$A$5:$A$20,0)))"

Originally Posted by Fluff13

How about

            "=AND(" & rng31.Address & "<>""Apples""," & rng31.Address & "<>""Oranges"",isnumber(MATCH(" & rng32.Address & ",Sheet5!$A$5:$A$20,0)))"

Hello Fluff13
Thank you for answer.
I changed to your solution and that gave me a Run-time error 5: "Invalid procedure call or argument"
I then changed all commas to semi-colons which resulted in all cells in rng30 became formatted according to code.
FYI, each cell in rng30 are formatted as "General" and contains first name, space and surname. Column "A" in "Sheet5" also formatted as "General".
I tried with istext but then nothing happens. Also noticed that "isnumber" doesn't change to "IsNumber".
Any idea of what's wrong?

Thanks in advance

Can you supply a copy of your workbook, as that makes it a lot easier to debug.

Hello Fluff13
Thanks alot for your input. Your solution with range = " & rng31.Address & " made me think a bit different and then I solved the problem with this solution.

j = 42
"=AND(AND(" & rng31.Address & "<>""Apples"";" & rng31.Address & "<>""Oranges"";MATCH(C" & j & ";Sheet5!$A$5:$A$20;0)))"
j = j + 18

It seems to work but if you can see anything not so good with code, I would appreciate if you point it out.

Thanks

That looks fine & glad you sorted it.