Defining a Range Based on Cell Color and a String-CodePudding

I am wanting to make a range of all cells in C4:Z104 that contain a letter AND either has a color index of white or grey (2 or 15 or 16). There are cells with other colors that should not be included in the range. Note that the cells are non-numeric.

Below is code that I'm trying to debug. It throws an error. I've tried using IsEmpty(), but read on other threads that this may not be the best for string values. Any help would be greatly appreciated.

Sub SelectByColorAndString()
  Dim cell As Range, u As Boolean
  For Each cell In Range("C4:Z104")
    
    If cell.Interior.ColorIndex = 2 or cell.Interior.ColorIndex = 15 or  cell.Interior.ColorIndex = 16 or & Not (IsNumeric(cell.Value) then
      If u = False Then cell.Select:  u = True
      Range(Selection.Address & "," & cell.Address).Select
    
End If
  Next
End Sub

Also, if any cell in any one of these 100 rows is included, the range should include the string value that is in column 1 of that row.

Thank you, MST

I've tried the following code but am receiving an error. I wanted all cells that are white or grey that have a letter in them to be selected.

Sub SelectByColorAndString()
  Dim cell As Range, u As Boolean
  For Each cell In Range("C4:Z104")
    
    If cell.Interior.ColorIndex = 2 or cell.Interior.ColorIndex = 15 or  cell.Interior.ColorIndex = 16 or & Not (IsNumeric(cell.Value) then
      If u = False Then cell.Select:  u = True
      Range(Selection.Address & "," & cell.Address).Select
    
End If
  Next
End Sub

CodePudding user response：

VBA uses And not & for boolean logic, and you need some parentheses around the tests for fill color.

Try this:

Sub SelectByColorAndString()
    Dim cell As Range, u As Boolean, ci As Long, rng As Range
    For Each cell In ActiveSheet.Range("C4:Z104").Cells
        ci = cell.Interior.ColorIndex
        If (ci = 2 Or ci = 15 Or ci = 16) And Not IsNumeric(cell.Value) Then
            If rng Is Nothing Then
                Set rng = cell
            Else
                Set rng = Application.Union(rng, cell)
            End If
        End If
    Next cell
    
    If Not rng Is Nothing Then rng.Select
End Sub