• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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

0
kazmdav
Asked:
kazmdav
  • 5
  • 4
1 Solution
 
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
Michael FowlerSolutions ConsultantCommented:
hmmmmm

try

Target.Interior.Pattern = xlNone

or

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

Michael
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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now