I am trying to create a quick automated way to color a range of cells in my worksheet based on their value (string).
(First text sub-string in the cell, up to a defined separator - e.g. "Germany;" for a cell containing "Germany;France;Italy;" - would get a fill color I defined for "Germany;" in a Lookup table (e.g. "blue").

I tried doing it via Conditional Formatting and got the results I wanted, but the recalculating it was doing every time I edited a cell made this solution impractical for me.


So, I looked into doing it using VBA code and mocked something up as a test:

Sub ColorCells1()
   Dim myRange As Range
   Dim i As Long, j As Long
  
   Set myRange = Range("B2:AAA255")
   For i = 1 To myRange.Rows.Count
      For j = 1 To myRange.Columns.Count
         If myRange.Cells(i, j).Value = "" Then
            myRange.Cells(i, j).Interior.ColorIndex = 3
         Else
            myRange.Cells(i, j).Interior.ColorIndex = 1
         End If
      Next j
   Next i
End Sub
I couldn't believe how slow this was by comparison (I waited at least a minute before it was over).

Isn't VBA supposed to be the fastest way to get those things done, and if so, what's wrong with my code?


Thanks