Need help with code

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
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
 
Last edited:

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Try reversing the If Intersect logic...

Code:
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
 
Last edited:

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
Try reversing the If Intersect logic...

Rich (BB code):
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Ahh, it was actually the End WITH that was missing

Code:
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

Board Regular
Joined
Jan 29, 2003
Messages
62

ADVERTISEMENT

Many Thanks to you...

Thanks again,

Rob
 

master_of_none

Board Regular
Joined
Jan 29, 2003
Messages
62
I get a runtime error code 1004
Unable to set Name property of font class.

What can I do to fix this?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Is your sheet protected?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Board Regular
Joined
Jan 29, 2003
Messages
62
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,318
Messages
5,600,923
Members
414,416
Latest member
Nobu

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