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

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

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

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

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

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



"Executor" wrote in message
oups.com...
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

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

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

"PCLIVE" wrote in message
...
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



"Executor" wrote in message
oups.com...
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

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

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.

--
Regards,
Tom Ogilvy



"PCLIVE" wrote in message
...
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



"Executor" wrote in message
oups.com...
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

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

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

--
Regards,
Tom Ogilvy



"PCLIVE" wrote in message
...
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

"PCLIVE" wrote in message
...
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



"Executor" wrote in message
oups.com...
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

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

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"PCLIVE" wrote in message
...
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

"PCLIVE" wrote in message
...
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



"Executor" wrote in message
oups.com...
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

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

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

--
Stewart Rogers
DataSort Software, L.C.


"PCLIVE" wrote:

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

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

I said:
Interior.ColorIndex is not altered by conditional formatting.

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

--
Regards,
Tom Ogilvy

"PCLIVE" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy



"PCLIVE" wrote in message
...
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

"PCLIVE" wrote in message
...
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



"Executor" wrote in message
oups.com...
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

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

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


--


Gary


"PCLIVE" wrote in message
...
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