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 <ryguy7...@discussions.microsoft.com>
> > 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
> >
|
Similar Threads
