How to exclude a few cells in a row when I'm using Target.column.color

I want most of the cells in a row to be magenta if they are modified, & thus my code is below. However, I want to exclude cells 16, 22 & 23 from becoming magenta.  I tried using vbwhite  (per code below) but the result is that the cell border is also removed.  

I figure its a simple answer but I cannot find one.
If Target.Column >= 1 Then Target.Interior.Color = vbMagenta

Open in new window

If Target.Column = 16 Or Target.Column = 22 Or Target.Column = 23 Then Target.Interior.Color = vbWhite

Open in new window

kazmdavAsked:
Who is Participating?
 
Michael FowlerConnect With a Mentor Solutions ConsultantCommented:
hmmmmm

try

Target.Interior.Pattern = xlNone

or

Target.Interior.ColorIndex = xlNone  'or xlColorIndexAutomatic or xlColorIndexNone

Michael
0
 
Michael FowlerSolutions ConsultantCommented:
Use xlNone instead of vbWhite to remove fill

Michael
0
 
Michael FowlerSolutions ConsultantCommented:
That is
If Target.Column = 16 Or Target.Column = 22 Or Target.Column = 23 Then Target.Interior.Color = xlNone

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
kazmdavAuthor Commented:
Thanks Michael. I tried xlNone and those cells change to a soft aqua colour.

Hmm, I wonder if there is some default set somewhere else?
0
 
kazmdavAuthor Commented:
oh you might want the code I have under the 'sheet1' object:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column >= 1 Then Target.Interior.Color = vbMagenta
    If Target.Column = 16 Or Target.Column = 22 Or Target.Column = 23 Then Target.Interior.Color = xlNone
    If Target.Row = 1 Then Target.Interior.Color = vbGreen
        
End Sub

Open in new window

0
 
Michael FowlerSolutions ConsultantCommented:
Not sure why you would get an aqua colour

Try

    If Target.Row = 1 Then
        Target.Interior.Color = vbGreen
    ElseIf Target.Column = 16 Or Target.Column = 22 Or Target.Column = 23 Then
        Target.Interior.Color = xlNone
    Else
        Target.Interior.Color = vbMagenta
    End If


   

0
 
kazmdavAuthor Commented:
Thanks Michael. I tried it but those cells (16. 22 & 23) still turn aqua.

I have checked if any conditional formatting = nothing there. Also checked if any data validation = nothing there.
0
 
kazmdavAuthor Commented:
Thank very much Michael. Target.Interior.Pattern = xlNone work perfectly!
0
 
kazmdavAuthor Commented:
I love the dedication to finding an answer.  Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.