Need help with code

[master_of_none]

I need help putting another code in the same sub.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Set rng = Range("O2:O501")

If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Value <> "X" Then
With Target
.Value = "X"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 3
End With

Plus this one



Set rng = Range("P2:P501")

If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Value <> "X" Then
With Target
.Value = "X"
With .Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.ColorIndex = 5
End With

 

[Jonmo1]

Try reversing the If Intersect logic...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set Rng = Range("O2:O501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
            .ColorIndex = 3
        End With
    End If
End If
Set Rng = Range("P2:P501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
            .ColorIndex = 5
        End With
    End If
End If

 

[master_of_none]

Try reversing the If Intersect logic...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set Rng = Range("O2:O501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
            .ColorIndex = 3
        End With
    End If
End If
Set Rng = Range("P2:P501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
            .Name = "Arial"
            .FontStyle = "Bold"
            .Size = 14
            .ColorIndex = 5
        End With
    End If
End If

I get an error "End if with outblock If"

 

[Jonmo1]

Ahh, it was actually the End WITH that was missing

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Set Rng = Range("O2:O501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 14
                .ColorIndex = 3
            End With
        End With
    End If
End If
Set Rng = Range("P2:P501")
If Not Intersect(Target, Rng) Is Nothing Then
    If Target.Value <> "X" Then
        With Target
            .Value = "X"
            With .Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 14
                .ColorIndex = 5
            End With
        End With
    End If
End If
End Sub

 

[master_of_none]

Many Thanks to you...

Thanks again,

Rob

 

[master_of_none]

I get a runtime error code 1004
Unable to set Name property of font class.

What can I do to fix this?

 

[Jonmo1]

Is your sheet protected?

 

[master_of_none]

yes, but the cells are not locked

 

[Jonmo1]

yes, but the cells are not locked

I don't think that matters....

Try this, adjust SheetName to whatever is the appropriate sheet you're working on. And put in the correct password, or remove "PasswordHere" if there is no password on the protection...


Sheets("SheetName").Unprotect "PasswordHere"

rest of code here

Sheets("SheetName").Protect "PasswordHere"

 

[master_of_none]

I don't think that matters....

Try this, adjust SheetName to whatever is the appropriate sheet you're working on. And put in the correct password, or remove "PasswordHere" if there is no password on the protection...


Sheets("SheetName").Unprotect "PasswordHere"

rest of code here

Sheets("SheetName").Protect "PasswordHere"

Do I post the unprotect before the "worksheet change event sub" line or after