Colouring cells if last character is a space

[Jesseke]

Hello,

Here below you can find vba code to color cells if the last character or first character is a space.
I tried the vba code in a test file and it worked, but when I go to 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"

In the meantime I checked my worksheet and the naming is correct.
Hope anybody can give me a hint about the error.

Please Login or Register  to view this content.

[FDibbins]

Not sure about VBA, but this simple formula should work in CF...
=AND(K2<>"",OR(LEFT(K2,1)=" ",RIGHT(K2,1)=" "))

[Jesseke]

FDibbins, thanks for your suggestion but my range of cells (columns (BP) and rows (2012)) is so big that it is quite impossible to do it on this manner.

[FDibbins]

OK so perhaps you could incorporate that test into your code?

All you really need to test of is...
- the cell isnt blank
- LEFT(K2,1)=" "
- RIGHT(K2,1)=" "

[Jesseke]

OK so perhaps you could incorporate that test into your code?

All you really need to test of is...
- the cell isnt blank
- LEFT(K2,1)=" "
- RIGHT(K2,1)=" "

Am I understand you correctly that I need to insert these tests in each cell again? Remember as explained: I have thousands of cells to check. Only workable quick manner is vba if you ask me. I'm working for a big company and may not modify cells, I may only add macro's to check some things because this file will be used for other departments too.

[LJMetzger]

Hi,

Try the following code. The first Macro that uses 'UsedRange' tests all cells in the rectangular area. The second Macro only tests cells that have a typed in value, or a value created with a formula.

Please Login or Register  to view this content.

Lewis

[Jesseke]

Hi,

Try the following code. The first Macro that uses 'UsedRange' tests all cells in the rectangular area. The second Macro only tests cells that have a typed in value, or a value created with a formula.

Please Login or Register  to view this content.

Lewis

Thanks Lewis