VBA - Formating interior color, but neither for a single cell, nor entire row

Question

I can not use condtional formating as it makes the excel document sheet very sluggish, therefore I would like to use VBA for this.

The code I have works, but I need a slight adjustment. Please see the issue described below the code. This is the code used:

Sub abcd()

For Each r In Range("B8:B500").SpecialCells(xlCellTypeConstants)
r.Interior.ColorIndex = xlNone
If r.Value Like "1" Then r.Interior.Color = vbYellow
If r.Value Like "2" Then r.Interior.Color = vbRed

Next
End Sub

Problem/Question:

The code under works, however it only makes the cell containing "1" and "2" yellow/red.

Lets say the cell value of B9 is 1, then I would like the range A9:T9 to be yellow.

Or in general terms: the range Ax:Tx where x could be any number.

PS: I do not wish to color the entire rows, only from A to T.

Answer 1

It surprises me that using condition formatting makes your Excel sluggish. Anyhow, if you want to go with VBA:
Your question is not about formatting itself but how you can access the cells from col A to T in a row. There are a lot of ways to do so. When r contains the cell with the value 1 (or 2) and is in column B, you can for example use any of these:

r.Offset(0, -1).Resize(1, 20).Interior.Color = vbYellow
Range(Cells(r.row, 1), Cells(r.row, 20).Interior.Color = vbYellow 
Range(Cells(r.row, "A"), Cells(r.row, "T").Interior.Color = vbYellow 
Range("A" & r.row & ":T:" r.row).Interior.Color = vbYellow  

The first method uses the Offset-function to access the cell from column A (-1 column away from column B) and the Resize-function to get a Range with 1 row height and 20 columns with.

The second and third method uses the Range-function with two parameters, defining the start and end cell of the Range. Those cells are defined by the Cells-function that get the row and column number as parameter. The column can either be specified by number (1 = A, 20 = T) or with the column character.

The forth method uses the Range-function with one parameter defining the range as you do in Excel (eg "A2:T2" for row 2)

---

Some remarks:

(o) If you want to check if a cell is equal to 1, you should use

If r.Value = 1 Then

Use the like operator only when checking for patterns, eg if you want to check if the cell contains any 1 (like "A1" or "123"). In that case you need to specify wildcards and use like:

If r.Value like "1" Then

(o) When you want your code to react whenever a value is entered (as the condition formatting would do), you need to react on the Worksheet_Change event. In that case you would need to check only the modified cell(s) - those are passed as parameter target:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim cell As Range
    For Each cell In Target
        If Intersect(cell, Range("B2:B500")) Then
            Dim r As Range
            Set r = cell.Offset(0, -1).Resize(1, 20)
            If cell.Value = 1 Then
                r.Interior.Color = vbYellow
            ElseIf cell.Value = 2 Then
                r.Interior.Color = vbRed
            Else
                r.Interior.ColorIndex = xlNone
            End If
        End If
    Next
End Sub