VBA/MACRO to highlight modified cells

[VictorKZ]

Hello everyone!

I have a Macro that selects all cells with data.
Now I need to change this Macro to also highlight only the cells that contains blanks spaces in the begging and in the end of the data inside the cells.
This blanks spaces are created normaly by the SpaceBar.
It´s similar to Left Trim and Right Trim, but I just need to highlight the cells that contains this blanks spaces.

Can anyone help me?
Thanks!

Sub Select_All_Cells_with_Data()

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

End Sub

 

[Joe4]

Here is one way:

Sub Select_All_Cells_with_Data()

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

'Highlight cells
For Each cell In Selection
    If Left(cell, 1) = " " And Right(cell, 1) = " " Then cell.Interior.Color = 65535
Next cell

End Sub

 

[VictorKZ]

Thank you very much !!
I changed only the AND to IF.

Sub Select_All_Cells_with_Data()

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

'Highlight cells
For Each cell In Selection
    If Left(cell, 1) = " " Then cell.Interior.Color = 65535
    If Right(cell, 1) = " " Then cell.Interior.Color = 65535
Next cell

End Sub

 

[Joe4]

Thank you very much !!
I changed only the AND to IF.

Sub Select_All_Cells_with_Data()

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

'Highlight cells
For Each cell In Selection
    If Left(cell, 1) = " " Then cell.Interior.Color = 65535
    If Right(cell, 1) = " " Then cell.Interior.Color = 65535
Next cell

End Sub

You are welcome!

I thought you wanted an AND condition (both must be met):

highlight only the cells that contains blanks spaces in the begging and in the end of the data inside the cells.

But if you want an "OR" condition, no need to add an extra IF, just change the AND to an OR, i.e.

Sub Select_All_Cells_with_Data()

Set Rng = ActiveSheet.UsedRange

Rng.Cells(1, 1).Select

For i = 1 To Rng.Rows.Count
    For j = 1 To Rng.Columns.Count
        If Rng.Cells(i, j) <> "" Then
            Union(Selection, Rng.Cells(i, j)).Select
        End If
    Next j
Next i

'Highlight cells
For Each cell In Selection
    If Left(cell, 1) = " " Or Right(cell, 1) = " " Then cell.Interior.Color = 65535
Next cell

End Sub