Font Size in cell is not as code states

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
Office Version
  1. 2007
Platform
  1. Windows
Hi,
I am using the following code.
My problem is that when i add a hyperlink into this cell the Font Size should be 10 but its actually say 22

If i type manually in the same cell or actuall any in column D then all is well and the Font Size is 10 as it should be.

Code:
Private Sub Worksheet_Activate()    
    Range("B7").Select
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Name = "Calibri"
    Range("D13:D100").BorderAround xlContinuous, xlThin
    Range("D13:D100").BorderAround xlContinuous, xlThin
End Sub

Thanks
 
Last edited by a moderator:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi,

I think I see what you are saying but correct me if I am wrong.
The font change to 10 is done on the range when the worksheet is activated and you then edit one of the entries after to create a hyperlink. It appears the hyperlink text
is then reverting to the workbooks default font size, which is set in File Options, which if you check should show 22?

To confirm - if you select another sheet and go back the hyperlink text will change to size 10?

I would say it's a bug....

I have tried a Worksheet Event and that doesn't seem to detect a change in the cell when you modify it to a hyperlink either..

Anyone cast some light???
 
Upvote 0
I tried the following 'bodge':
Add a hyperlink to the cell then click anywhere in column E

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 5 Then
Range("D13:D100").Font.Size = 10
End If

End Sub
 
Upvote 0
I tried the following 'bodge':
Add a hyperlink to the cell then click anywhere in column E

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Column = 5 Then
Range("D13:D100").Font.Size = 10
End If

End Sub

When i put a hyperlink in the cell i do it like this.
Right click,Hyperlink, then paste link in address field.
For me this still returns the huge text font.

If i just paste the hyperlink straight into a cell then yes its at the size 10 font but when you click the link it doesnt open up the url
 
Upvote 0
Hi,

you need to paste the above code underneath your worksheet activate code.
Right-click, hyperlink and paste the address as you do - the font will increase in size.
Left click column E to activate selection change code and that should change the font back to size 10

Code:
Private Sub Worksheet_Activate()
    Range("B7").Select
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Name = "Calibri"
    Range("D13:D100").BorderAround xlContinuous, xlThin
    Range("D13:D100").BorderAround xlContinuous, xlThin
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then
Range("D13:D100").Font.Size = 10
End If
End Sub
 
Last edited:
Upvote 0
Morning,

My now current code is shown below.

I have 2 questions / requests please.

After completing the hyperlink as opposed to clicking in column E to then force the hyperlink to font size 10 can a range of 2 - 4 be used instead ?

When the hyperlink font size is changed to 10 i also need it to change to uppercase.
Many thanks, i did try Range("D13:D100").Value = Ucase but it didnt work for me


Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Column = 5 Then
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Name = "Calibri"
    Range("D13:D100").BorderAround xlContinuous, xlThin
    Range("D13:D100").BorderAround xlContinuous, xlThin
    Range("B7").Select
End If
    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 = "B"
    myEndCol = "D"


'   *** Specify start row ***
    myStartRow = 7
    
'   Use first column to find the last row
    myLastRow = Cells(Rows.Count, myStartCol).End(xlUp).Row
    
'   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
 
Upvote 0
Hi,

you can use the application.intersect for a particular range. I used E2to E4

I found this for the uppercase. Works for me.
https://stackoverflow.com/questions...percase-without-looping-through-all-the-cells

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Application.Intersect(Target, Range("E2:E4")) Is Nothing Then
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Font.Size = 10
    Range("D13:D100").Name = "Calibri"
    Range("D13:D100").BorderAround xlContinuous, xlThin
    Range("D13:D100").BorderAround xlContinuous, xlThin
   [D13:D100] = [INDEX(UPPER(D13:D100),)]
    Range("B7").Select
End If
 
Upvote 0
If you want the target range over several columns you can use A2:Z4 for example
 
Upvote 0
You're welcome - thanks for the feedback.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

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