onChange Event

KJP0422

New Member
Joined
Apr 28, 2016
Messages
21
Hello,

I am using Excel 2010 and I am trying to add an OnChange Event onto the sheet where I need to increase the font size. The cell R29 (merged cell extends to V29) holds an email address and once a user supplies the value it reduces the font size to 6. To compensate for this I added the following code. the code is not working as the font size remains 6. Any clues on what I am doing wrong? Thanks for your help in advance.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$R$29" Then
Range("R29").Select

With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With

End If

End Sub
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
I think you want the "Worksheet_Change" event, which runs upon that cell being updated.
The one you have chosen, "Worksheet_SelectionChange", runs upon the cell first being selected. So in order to get that code to do what you want it to with that event, you would need to re-select that cell after updating it (moving off of it does not trigger that code).
 
Upvote 0
Thank you, Thank you, Snoopy Happy Happy Dance going on now. I would have never figured that one out. I modified to the following and it is working as anticipated.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$R$29" Then

Range("R29").Select

With Selection.Font
.Name = "Calibri"
.FontStyle = "Regular"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.TintAndShade = 0
.ThemeFont = xlThemeFontMinor
End With

End If

End Sub
 
Upvote 0
You are welcome. You don't actually need to Select the cell, since you already checked to see that the Target cell (the active cell that was just updated) is the one you are looking for.

So you could just simplify it to:
Code:
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]

[COLOR=#333333]    If Target.Address = "$R$29" Then[/COLOR]

[COLOR=#333333]        With Target.Font
[/COLOR][COLOR=#333333]            .Name = "Calibri"[/COLOR]
[COLOR=#333333]            .FontStyle = "Regular"
[/COLOR][COLOR=#333333]            .Size = 12[/COLOR]
[COLOR=#333333]            .Strikethrough = False[/COLOR]
[COLOR=#333333]            .Superscript = False[/COLOR]
[COLOR=#333333]            .Subscript = False[/COLOR]
[COLOR=#333333]            .OutlineFont = False[/COLOR]
[COLOR=#333333]            .Shadow = False[/COLOR]
[COLOR=#333333]            .TintAndShade = 0[/COLOR]
[COLOR=#333333]            .ThemeFont = xlThemeFontMinor[/COLOR]
[COLOR=#333333]        End With[/COLOR]

[COLOR=#333333]    End If[/COLOR]

[COLOR=#333333]End Sub[/COLOR]
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

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
Back
Top