use code but omit specific column

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,913
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I have a working code as supplied below.
It works as it should but now i require it to NOT have any action for column D
Other than not having it work for that column then all is fine

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim myRange As Range
 
    If Target.Cells.Count > 1 Then Exit Sub
   
    Application.ScreenUpdating = False
   
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "I"
 
'   *** Specify start row ***
    myStartRow = 8
   
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row + 1
   
'   Build range to apply this to
    Set myRange = Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol))
   
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
   
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
   
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True
 
End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
After this line:
Code:
    If Target.Cells.Count > 1 Then Exit Sub
add this line:
Code:
    If Target.Column = 4 Then Exit Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,913
Office Version
  1. 2007
Platform
  1. Windows
Thanks,
I didnt quite explain myself 100%

I also mean it to not change colour for column D
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
As long as your last column is always after column D, this should work:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim myRange As Range
 
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Exclude changes to column D
    If Target.Column = 4 Then Exit Sub
   
    Application.ScreenUpdating = False
   
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "I"
 
'   *** Specify start row ***
    myStartRow = 8
   
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row + 1
   
'   Build range to apply this to (exclude column D)
    Set rng1 = Intersect(Columns("A:C"), Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol)))
    Set rng2 = Intersect(Columns("E:" & myEndCol), Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol)))
    Set myRange = Union(rng1, rng2)
   
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
   
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
   
'   Highlight the row and column that contain the active cell
    Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)).Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True
 
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,913
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Still the same.

I click YES on the msgbox followed by ok on the success message.

I now see that cell D901 is pink.
If i select cell D901 or actualy any cell in column D nothing happens,which is good.

But if i then click any cell ON row 901 i then see cel D901 change from its Pink to the colour in the code of which is Interior.ColorIndex = 8
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim myStartCol As String
    Dim myEndCol As String
    Dim myStartRow As Long
    Dim myLastRow As Long
    Dim rng1 As Range
    Dim rng2 As Range
    Dim myRange As Range
    Dim myRange2 As Range
 
    If Target.Cells.Count > 1 Then Exit Sub
    
'   Exclude changes to column D
    If Target.Column = 4 Then Exit Sub
   
    Application.ScreenUpdating = False
   
'   *** Specify columns to apply this to ***
    myStartCol = "A"
    myEndCol = "I"
 
'   *** Specify start row ***
    myStartRow = 8
   
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row + 1
   
'   Build range to apply this to (exclude column D)
    Set rng1 = Intersect(Columns("A:C"), Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol)))
    Set rng2 = Intersect(Columns("E:" & myEndCol), Range(Cells(myStartRow, myStartCol), Cells(myLastRow, myEndCol)))
    Set myRange = Union(rng1, rng2)
   
'   Clear the color of all the cells in range
    myRange.Interior.ColorIndex = 6
   
'   Check to see if cell selected is outside of range
    If Intersect(Target, myRange) Is Nothing Then Exit Sub
   
'   Highlight the row and column that contain the active cell
    Set myRange2 = Intersect(Range(Cells(Target.Row, myStartCol), Cells(Target.Row, myEndCol)), myRange)
    myRange2.Interior.ColorIndex = 8
    Target.Interior.Color = vbGreen
    Application.ScreenUpdating = True
 
End Sub
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,913
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,
Thanks very much.
Its nearly there but giving me a headache so im going to leave it with what it currently does.

Thanks for the help this evening.
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,913
Office Version
  1. 2007
Platform
  1. Windows
Joe4

Now a slight change and spot on.

Thanks very much once again
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,717
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,019
Messages
5,628,180
Members
416,297
Latest member
Kara Payne

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top