VBA Sheet color change condition

[VictorKZ]

Hello everybody,
I would like to change the colors of certain sheets, which meet this condition => If Left(cell, 1) = " " Or Right(cell, 1) = " " Then cell.Interior.Color = 65535

Sub Select_All_Cells_with_Data()

Dim sht
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

For Each sht In ThisWorkbook.Worksheets
  sht.Tab.ColorIndex = 6
 
Next sht

End Sub

Thanks for listening

 

[rollis13]

Your macro does what you are asking for as long as your data has a leading or a trailing space. It will highlight the cell even if you meet both conditions.

 

[HaHoBe]

Hi VictorKZ,

from what I understand:

Sub MrE1216932()
'https://www.mrexcel.com/board/threads/vba-sheet-color-change-condition.1216932/

Dim wks As Worksheet
Dim rngCell As Range
Dim blnColTab As Boolean

For Each wks In ThisWorkbook.Worksheets
  blnColTab = False
  For Each rngCell In wks.UsedRange
    With rngCell
      If .Value <> "" And (Left(.Value, 1) = " " Or Right(.Value, 1) = " ") Then
        .Interior.Color = vbYellow
        blnColTab = True
      End If
    End With
  Next rngCell
  If blnColTab Then wks.Tab.ColorIndex = 6
Next wks

End Sub

Ciao,
Holger

 

[VictorKZ]

Thanks a lot for the help!