Copy Non-Colored Cells to a New Sheet
 

I tried the following macro to copy/paste non-colored cells to a sheet called
€˜Copy If Non Color. For some reason, all cells are copied to that sheet,
not just the non-colored cells. What am I doing wrong?

Sub noncolorcopier()
Dim w As Long
y = 1
Set z = ActiveSheet.UsedRange
nLastRow = z.Rows.Count + z.Row - 1
For w = 1 To nLastRow
If is_it_non_red(w) Then
Set rc = Cells(w, 1).EntireRow
Set rd = Sheets("Copy If Non Color").Cells(y, 1)
rc.Copy rd
y = y + 1
End If
Next


End Sub


Function is_it_non_red(w As Long) As Boolean
is_it_non_red = False
For x = 1 To Columns.Count
If Cells(w, x).Interior.ColorIndex = xlNone Or Cells(w,
x).Interior.ColorIndex = xlWhite Then
is_it_non_red = True
Exit Function
End If
Next
End Function


Regards,
Ryan---


--
RyGuy

Copy Non-Colored Cells to a New Sheet
 

On Jun 11, 3:09 pm, ryguy7272
wrote:
I tried the following macro to copy/paste non-colored cells to a sheet called
‘Copy If Non Color’. For some reason, all cells are copied to that sheet,
not just the non-colored cells. What am I doing wrong?

Sub noncolorcopier()
Dim w As Long
y = 1
Set z = ActiveSheet.UsedRange
nLastRow = z.Rows.Count + z.Row - 1
For w = 1 To nLastRow
If is_it_non_red(w) Then
Set rc = Cells(w, 1).EntireRow
Set rd = Sheets("Copy If Non Color").Cells(y, 1)
rc.Copy rd
y = y + 1
End If
Next

End Sub

Function is_it_non_red(w As Long) As Boolean
is_it_non_red = False
For x = 1 To Columns.Count
If Cells(w, x).Interior.ColorIndex = xlNone Or Cells(w,
x).Interior.ColorIndex = xlWhite Then
is_it_non_red = True
Exit Function
End If
Next
End Function

Regards,
Ryan---

--
RyGuy

RyGuy,

I may be a better idea to zotz out the values on the back end after
you have pasted (with formats):

Sub ZotzNoColors()
Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("Whatever")
For Each cell In dataRange
If cell.Interior.ColorIndex = xlNone Then
cell = ""
End If
End Sub

Copy Non-Colored Cells to a New Sheet
 

VERY interesting. I never thought of that before. How would I apply this
technique to the whole row? For instance, if there is a colored cell
somewhere in the row, make the entire row = "", or just delete the row, or
something along those lines.

Code Below (not working):
Sub ZotzNoColors()
Dim i As Long
Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("Whatever")
For Each cell In dataRange
If cell.Interior.ColorIndex < xlNone Then
Set rc = Cells(i, 1).EntireRow
rc = ""
End If
Next cell
End Sub


--
RyGuy


"SteveM" wrote:

On Jun 11, 3:09 pm, ryguy7272
wrote:
I tried the following macro to copy/paste non-colored cells to a sheet called
€˜Copy If Non Color. For some reason, all cells are copied to that sheet,
not just the non-colored cells. What am I doing wrong?

Sub noncolorcopier()
Dim w As Long
y = 1
Set z = ActiveSheet.UsedRange
nLastRow = z.Rows.Count + z.Row - 1
For w = 1 To nLastRow
If is_it_non_red(w) Then
Set rc = Cells(w, 1).EntireRow
Set rd = Sheets("Copy If Non Color").Cells(y, 1)
rc.Copy rd
y = y + 1
End If
Next

End Sub

Function is_it_non_red(w As Long) As Boolean
is_it_non_red = False
For x = 1 To Columns.Count
If Cells(w, x).Interior.ColorIndex = xlNone Or Cells(w,
x).Interior.ColorIndex = xlWhite Then
is_it_non_red = True
Exit Function
End If
Next
End Function

Regards,
Ryan---

--
RyGuy

RyGuy,

I may be a better idea to zotz out the values on the back end after
you have pasted (with formats):

Sub ZotzNoColors()
Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("Whatever")
For Each cell In dataRange
If cell.Interior.ColorIndex = xlNone Then
cell = ""
End If
End Sub

Copy Non-Colored Cells to a New Sheet
 

Oh, just got it. The code is like this:
Sub noncolorcopier()
Dim w As Long
y = 1
Set z = ActiveSheet.UsedRange
nLastRow = z.Rows.Count + z.Row - 1
For w = 1 To nLastRow
If is_it_non_red(w) Then
Set rc = Cells(w, 1).EntireRow
Set rd = Sheets("Copy If Non Color").Cells(y, 1)
rc.Copy rd
y = y + 1
End If
Next


End Sub


Function is_it_non_red(w As Long) As Boolean
is_it_non_red = True
For x = 1 To Columns.Count
If Cells(w, x).Interior.ColorIndex < xlNone Then
is_it_non_red = False
Exit Function
End If
Next
End Function

I just flipped these:
is_it_non_red = False
is_it_non_red = True

To these:
is_it_non_red = True
is_it_non_red = False


Hope this helps others.
Ryan---

--
RyGuy


"ryguy7272" wrote:

VERY interesting. I never thought of that before. How would I apply this
technique to the whole row? For instance, if there is a colored cell
somewhere in the row, make the entire row = "", or just delete the row, or
something along those lines.

Code Below (not working):
Sub ZotzNoColors()
Dim i As Long
Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("Whatever")
For Each cell In dataRange
If cell.Interior.ColorIndex < xlNone Then
Set rc = Cells(i, 1).EntireRow
rc = ""
End If
Next cell
End Sub


--
RyGuy


"SteveM" wrote:

On Jun 11, 3:09 pm, ryguy7272
wrote:
I tried the following macro to copy/paste non-colored cells to a sheet called
€˜Copy If Non Color. For some reason, all cells are copied to that sheet,
not just the non-colored cells. What am I doing wrong?

Sub noncolorcopier()
Dim w As Long
y = 1
Set z = ActiveSheet.UsedRange
nLastRow = z.Rows.Count + z.Row - 1
For w = 1 To nLastRow
If is_it_non_red(w) Then
Set rc = Cells(w, 1).EntireRow
Set rd = Sheets("Copy If Non Color").Cells(y, 1)
rc.Copy rd
y = y + 1
End If
Next

End Sub

Function is_it_non_red(w As Long) As Boolean
is_it_non_red = False
For x = 1 To Columns.Count
If Cells(w, x).Interior.ColorIndex = xlNone Or Cells(w,
x).Interior.ColorIndex = xlWhite Then
is_it_non_red = True
Exit Function
End If
Next
End Function

Regards,
Ryan---

--
RyGuy

RyGuy,

I may be a better idea to zotz out the values on the back end after
you have pasted (with formats):

Sub ZotzNoColors()
Dim cell As Range
Dim dataRange As Range

Set dataRange = Range("Whatever")
For Each cell In dataRange
If cell.Interior.ColorIndex = xlNone Then
cell = ""
End If
End Sub