Clear color but not pattern?

I am having issues with the cell color changing and updating, and removing
pattern information in the same cell.

I need to look through a data and color it a certain color and when the data
is revised, I uncolor it. Users have added patterns to denote other stuff and
the remove color as desribed below, also removes the pattern. Is there a way
to remove the color and retain the pattern?

If Cells(x, y).Interior.ColorIndex = 8 Then
Cells(x, y).Interior.ColorIndex = xlNone
End If

Thanks,
Jay

Try this:

If Cells(x, y).Interior.ColorIndex = 8 Then
myPattern = Cells(x,y).Interior.Pattern
Cells(x, y).Interior.ColorIndex = xlNone
Cells(x, y).Interior.Pattern = myPatter
End If

jayklmno wrote:
> I am having issues with the cell color changing and updating, and removing
> pattern information in the same cell.
>
> I need to look through a data and color it a certain color and when the data
> is revised, I uncolor it. Users have added patterns to denote other stuff and
> the remove color as desribed below, also removes the pattern. Is there a way
> to remove the color and retain the pattern?
>
> If Cells(x, y).Interior.ColorIndex = 8 Then
> Cells(x, y).Interior.ColorIndex = xlNone
> End If
>
> Thanks,
> Jay

Retain the pattern with a variable and reapply it.
i.e.

Dim myPattern as long

If Cells(x, y).Interior.ColorIndex = 8 Then
myPattern = Cells(x, y).Interior.Pattern
Cells(x, y).Interior.ColorIndex = xlNone
Cells(x, y).Interior.Pattern = myPattern
End If


--
Les Torchia-Wells


"jayklmno" wrote:

> I am having issues with the cell color changing and updating, and removing
> pattern information in the same cell.
>
> I need to look through a data and color it a certain color and when the data
> is revised, I uncolor it. Users have added patterns to denote other stuff and
> the remove color as desribed below, also removes the pattern. Is there a way
> to remove the color and retain the pattern?
>
> If Cells(x, y).Interior.ColorIndex = 8 Then
> Cells(x, y).Interior.ColorIndex = xlNone
> End If
>
> Thanks,
> Jay

Thanks, Gents!

"John Fuller" wrote:

> Try this:
>
> If Cells(x, y).Interior.ColorIndex = 8 Then
> myPattern = Cells(x,y).Interior.Pattern
> Cells(x, y).Interior.ColorIndex = xlNone
> Cells(x, y).Interior.Pattern = myPatter
> End If
>
> jayklmno wrote:
> > I am having issues with the cell color changing and updating, and removing
> > pattern information in the same cell.
> >
> > I need to look through a data and color it a certain color and when the data
> > is revised, I uncolor it. Users have added patterns to denote other stuff and
> > the remove color as desribed below, also removes the pattern. Is there a way
> > to remove the color and retain the pattern?
> >
> > If Cells(x, y).Interior.ColorIndex = 8 Then
> > Cells(x, y).Interior.ColorIndex = xlNone
> > End If
> >
> > Thanks,
> > Jay
>
>

Something like:

With Selection.Interior
sPattern = .Pattern
sPatternIndex = .PatternColorIndex
.ColorIndex = xlNone
.Pattern = sPattern
.PatternColorIndex = sPatternIndex
End With

So:

If Cells(x, y).Interior.ColorIndex = 8 Then
With Cells(x, y).Interior
sPattern = .Pattern
sPatternIndex = .PatternColorIndex
.ColorIndex = xlNone
.Pattern = sPattern
.PatternColorIndex = sPatternIndex
End With
End If

Regards

Trevor


"jayklmno" <jayklmno@discussions.microsoft.com> wrote in message
news:C83AEFCB-E202-45C1-AF5D-28367337E794@microsoft.com...
>I am having issues with the cell color changing and updating, and removing
> pattern information in the same cell.
>
> I need to look through a data and color it a certain color and when the
> data
> is revised, I uncolor it. Users have added patterns to denote other stuff
> and
> the remove color as desribed below, also removes the pattern. Is there a
> way
> to remove the color and retain the pattern?
>
> If Cells(x, y).Interior.ColorIndex = 8 Then
> Cells(x, y).Interior.ColorIndex = xlNone
> End If
>
> Thanks,
> Jay