Colouring cells if last or first character is a space

Question

Below is VBA code to color cells if the last character or first character is a space.

I tried a test file and it worked, but on the file that has to be checked, an error message appears:

"Run-time error '13': Type mismatch".

If I click on debug, the yellow text line to show the error jumps to

If Left(sheetArr(i, j), 1) = " " Then

I checked my worksheet and the naming is correct.

I have thousands of cells to check. I may not modify cells, I may only add macros to check things because this file will be used by other departments.

Sub trailingspace()
'
' Find preceding and trailing spaces in a cell.
' Color these cells pink.
'

    Dim i As Long, j As Long
    Dim sh As Worksheet
    Dim sheetArr As Variant
    Set sh = ThisWorkbook.Sheets("wording")
    sheetArr = sh.UsedRange
    rowC = sh.UsedRange.Rows.Count
    colC = sh.UsedRange.Columns.Count

    For i = 1 To rowC
        For j = 1 To colC
            If Left(sheetArr(i, j), 1) = " " Then
                sh.Cells(i, j).Interior.ColorIndex = 26
            End If

            If Right(sheetArr(i, j), 1) = " " Then
                sh.Cells(i, j).Interior.ColorIndex = 26
            End If
        Next j
    Next i
End Sub

Answer 1

Here is another alternative that avoids VBA entirely. I decided against using a formula that would include both leading and trailing spaces and separated out the rules just in case you (or someone looking at this question later) wanted a different format for each rule.

• Start by selecting the range you want to be checked for formatting.
• Create a new CF rule, selecting the 2nd option "Format only cells that contain".
• In the first dropdown box, select "Specific Text"
• In the 2nd box, select "beginning with"
• In the formula box, simply type a space
• Then select your desired formatting (which in your case, will be a pink fill)
• Repeat the steps above, except using "ending with" in this iteration

In the end, your result would look like the following: