Apply VBA Code to 2 Columns, then skip one and repeat.

[Ernestas98]

Hi All,

I have a piece of code here and I need to adjust the range to take into account new amendments made to the spreadsheet.

I am adding a new column before the code range and a column after every 2 columns in the code range. I will add a screenshot of the spreadsheet and where the new columns will be.

What I need the code to do is to work in the same columns it does now as otherwise it shifts by one and disrupts the whole spreadsheet.

Here's the code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 5 And Target.Column Mod 2 = 0 And Target.Row > 6 And Target.Row < 208 Then
        Cancel = True
        Select Case Target.Value
            Case "P"
                Target.Interior.ColorIndex = xlNone
                Target.Resize(, 2).Value = ""
            Case Else
                Target.Font.Name = "Wingdings 2"
                Target.Font.Size = 36
                Target.Interior.Color = vbGreen
                Target.Offset(, 1).Value = Date
                Target.Value = "P"
        End Select
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Columns will be added here (in red) added columns in red.PNG

[Norie]

It's not clear from the image exactly what the new set up is, can you upload a sample workbook?

[Ernestas98]

It's not clear from the image exactly what the new set up is, can you upload a sample workbook?

Attached here

[Sintek]

Welcome to our Forum...

Please take a moment to read our forum rules...here
Your post does not comply with Rule # 2
2. Programming code must be enclosed in code tags to improve readability.

 Your Code

So...Edit your post...Highlight the code and press the # button

[Ernestas98]

Welcome to our Forum...

Please take a moment to read our forum rules...
Your post does not comply with Rule # 2
2. Programming code must be enclosed in code tags to improve readability.

 Your Code

So...Edit your post...Highlight the code and press the # button

Updated, thank you

[Norie]

So starting in column G for every 3 columns you want the first 2 columns to trigger the code but not the 3rd column?

[Ernestas98]

So starting in column G for every 3 columns you want the first 2 columns to trigger the code but not the 3rd column?

Yes

So that the tick is in the "Shared?" column, the date in the "Date" and "likes" column is left for manual input

Thank you

[Norie]

Try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 6 And Target.Column Mod 3 = 1 And Target.Row > 6 And Target.Row < 208 Then
        Cancel = True
        Select Case Target.Value
            Case "P"
                Target.Interior.ColorIndex = xlNone
                Target.Resize(, 2).Value = ""
            Case Else
                Target.Font.Name = "Wingdings 2"
                Target.Font.Size = 36
                Target.Interior.Color = vbGreen
                Target.Offset(, 1).Value = Date
                Target.Value = "P"
        End Select
    End If
End Sub

[Ernestas98]

Try this.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column > 6 And Target.Column Mod 3 = 1 And Target.Row > 6 And Target.Row < 208 Then
        Cancel = True
        Select Case Target.Value
            Case "P"
                Target.Interior.ColorIndex = xlNone
                Target.Resize(, 2).Value = ""
            Case Else
                Target.Font.Name = "Wingdings 2"
                Target.Font.Size = 36
                Target.Interior.Color = vbGreen
                Target.Offset(, 1).Value = Date
                Target.Value = "P"
        End Select
    End If
End Sub

That worked perfectly! Thank you for saving the day again )