Font Size in cell is not as code states

[ipbr21054]

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.

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

 

[daverunt]

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???

 

[daverunt]

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

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

End Sub

 

[ipbr21054]

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

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

 

[daverunt]

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

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

 

[ipbr21054]

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

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

 

[daverunt]

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

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

 

[daverunt]

If you want the target range over several columns you can use A2:Z4 for example

 

[ipbr21054]

Many thanks for the help it works perfect.

Thank You

 

[daverunt]

You're welcome - thanks for the feedback.