Excel VBA - Conditional Formatting (Faster Method?)

TropicalMagic

New Member
Joined
Jun 19, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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:

SCREENSHOT 2.png




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!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,039
Office Version
  1. 2010
Platform
  1. Windows
you need to change this line:
VBA Code:
If arrA < arrB Then
to
VBA Code:
If arrA(x,1) < arrB(x,1) Then
also these lines won't work:
VBA Code:
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:
VBA Code:
cells(x, 1).Font.Color = RGB(156, 0, 6)

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

TropicalMagic

New Member
Joined
Jun 19, 2021
Messages
27
Office Version
  1. 365
Platform
  1. Windows
you need to change this line:
VBA Code:
If arrA < arrB Then
to
VBA Code:
If arrA(x,1) < arrB(x,1) Then
also these lines won't work:
VBA Code:
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:
VBA Code:
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!
 

Forum statistics

Threads
1,144,698
Messages
5,725,821
Members
422,643
Latest member
elwayfan446

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top