Excel VBA - Conditional Formatting (Faster Method?)

[TropicalMagic]

Hi all,



I would like to compare two ranges, A and B.



If the cell values in range A is less than the corresponding cell values in range B,

Change their Font.Color = RGB(156, 0, 6) and Interior.Color = RGB(255, 199, 206).


Here is my desired result and worksheet layout:

Here is my code so far, using arrays (does not work):


```

Dim ws As Worksheet

Dim x, LastRow As Long, arrA as Variant, arrB as Variant



Set ws = Workbooks("MyWorkbook.xlsx").Sheets("MyWorksheet")

LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row



arrA = ws.Range("A2:A" & LastRow).Value 'put the range in an array to make the iteration faster

arrB = ws.Range("B2:B" & LastRow).Value 'put the range in an array to make the iteration faster



For x = 1 To UBound(arrA)



If arrA < arrB Then

arrA(x, 1).Font.Color = RGB(156, 0, 6)

arrA(x, 1).Interior.Color = RGB(255, 199, 206)

End If

Next x

```



However, there is an error at the line “If arrwsk1G < arrwsk1H Then”,

Error 13: Type Mismatch



How do I fix the issue? I would like to use Excel VBA and arrays as I have been advised that arrays speed up the automation process greatly.



Many thanks!

 

[offthelip]

you need to change this line:

If arrA < arrB Then

to

If arrA(x,1) < arrB(x,1) Then

also these lines won't work:

arrA(x, 1).Font.Color = RGB(156, 0, 6)

arrA(x, 1).Interior.Color = RGB(255, 199, 206)

because you can't color an array!!! change them to:

cells(x, 1).Font.Color = RGB(156, 0, 6)

cells(x, 1).Interior.Color = RGB(255, 199, 206)

 

[TropicalMagic]

you need to change this line:

If arrA < arrB Then

to

If arrA(x,1) < arrB(x,1) Then

also these lines won't work:

arrA(x, 1).Font.Color = RGB(156, 0, 6)

arrA(x, 1).Interior.Color = RGB(255, 199, 206)

because you can't color an array!!! change them to:

cells(x, 1).Font.Color = RGB(156, 0, 6)

cells(x, 1).Interior.Color = RGB(255, 199, 206)

Brilliant! This was what I was looking for! Many thanks!!

Thanks for the clarifications as well, they cleared up alot of confusion I had about arrays!