Macro to select cells within a range in which the cell color is none

[PCLIVE]

Within a range, say A2:A27, I want to select only the cells whose shading is
none. How can I do this? I think I've seen similar postings in the past,
but I was unable to find them.

Thanks,
Paul

 

[Executor]

Hi Paul,

Try this:

Sub SelectWithBlancInterior()
Dim rngLoop As Range
Dim strSubSet As String
'
For Each rngLoop In Range("A2", "A27")
If rngLoop.Interior.ColorIndex = 0 Then
strSubSet = strSubSet & rngLoop.Address & ","
End If
Next
strSubSet = Left(strSubSet, Len(strSubSet) - 1)

Range(strSubSet).Select

End Sub


Hoop that helps,

Wouter

 

[PCLIVE]

Thanks for the response

I tried this and I get a "Run-time error '5': Invalid procedure call or
argument" at:
strSubSet = Left(strSubSet, Len(strSubSet) - 1)

I pasted the code exactly with no other code involved. Am I missing
something?

Thanks,
Paul

 

[PCLIVE]

I forgot to include this.

The cells that are being colored, are done so through conditional
formatting. I think that may be part of my problem. Is there a way to
apply your code to cells that have Conditional Formatting applied to them?

Thanks again,
Paul

 

[Tom Ogilvy]

that probably is because your interior is not a colorindex of zero (white)
but has not interior.colorindex (usually the default)

from the immediate window:
?xlNone
-4142

so with the code provided, if no cells are found, you can't do something
with -1 as the second argument to Left


Sub SelectWithBlancInterior()
Dim rngLoop As Range
Dim r as Ragne
'
For Each rngLoop In Range("A2", "A27")
If rngLoop.Interior.ColorIndex = xlNone Then
if r is nothing then
set r = rngLoop
else
set r = union(rngLoop,r)
end if
End If
Next
if not r is nothing then
r.Select
else
msgbox "None found"
end if
End Sub

concatenating a string with cell address is pretty constraining. Better to
use the union with a range reference.

 

[Tom Ogilvy]

It is best to check the condition that governs the conditional formatting.
Interior.ColorIndex is not altered by conditional formatting. Checking it
is extremely complex and inefficient - thus the advice to check the
condition itself.

However, the problem with error produced by the original code is checking
for zero instead of checking for xlNone would be my guess.

My revised code should select all your cells.

If you really want to check for the color produced by conditional
formatting, look at Chip Pearson's page on this:

http://www.cpearson.com/excel/CFColors.htm

 

[PCLIVE]

Tom,

Your revised code does in fact select all the cells in the range. Not just
the ones that I want. Apparently when a cell is shaded due to conditional
formatting, it is not the same as the cell.ColorIndex. So maybe there is
another way to do this. Would it be possible to have code that would
determine if FormatConditions(1) is true? Then this could become the
strSubSet or Union.

I appreciate the help.
Paul

 

[Guest]

The following code works if you do not want to check for conditional
formatting (CF). I do not know how to test to see if the cell has cf
required and which criteria was met. One possible solution is to format copy
the CF cell to a blank cell, remove cf then test the cell, the replace cf.

Sub SelectColorIndexNone()

Dim objC As Range, ObjSelectRange As Range, c As Range


For Each objC In Range("f6:f16")
If objC.Interior.ColorIndex = xlNone Then

'*** Add cell to range
If ObjSelectRange Is Nothing Then
Set ObjSelectRange = objC
Else
Set ObjSelectRange = Union(ObjSelectRange, objC)
End If '***
End If
Next

ObjSelectRange.Select
Set ObjSelectRange = Nothing
Set objC = Nothing

Exit Sub


End Sub

 

[Tom Ogilvy]

I said:
I also gave you a reference to a web page that gives you the code to do what
you describe.

 

[Gary Keramidas]

adapted from bob phillips

Sub GetUnShadedCells()
Dim cell As Range
Dim rng As Range

For Each cell In Range("A2:A27")
If cell.Interior.ColorIndex = xlNone Then
If rng Is Nothing Then
Set rng = cell
Else
Set rng = Union(rng, cell)
End If
End If
Next cell

If Not rng Is Nothing Then
rng.Select
End If

End Sub