Font size after hyperlink applied

[ipbr21054]

Hi,

The value in cell B is font size 12 but when i hyperlink the value it changes to 16
What must i do so after the hyperlink its 12 NOT 16

If i use my code in the select change event it works fine.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If Intersect(Target, Range("A8:I" & LastRow)) Is Nothing Then Exit Sub
    If Target.Column <> 4 And Target.Column <> 7 Then
        Range("A8:C" & LastRow).Interior.ColorIndex = 6
        Range("E8:F" & LastRow).Interior.ColorIndex = 6
        Range("H8:I" & LastRow).Interior.ColorIndex = 6
        Target.Interior.ColorIndex = 8

    End If
[COLOR=rgb(184, 49, 47)]    Application.ScreenUpdating = True
    Range("A8:I5000").Font.Size = 12
    Range("A8:I5000").Font.Bold = True
    Range("A8:I5000").HorizontalAlignment = xlCenter
    Range("B8:B5000").VerticalAlignment = xlCenter
    Range("B8:B5000").Font.Size = 12[/COLOR]
End Sub

BUT if i use the same code in the change event the font size will stay at 16

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 7 And Target.Row > 9 Then
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Target.Value = "" Then Target.Interior.Color = vbRed
    
    End If
    Application.ScreenUpdating = True
    Range("A8:I5000").Font.Size = 12
    Range("A8:I5000").Font.Bold = True
    Range("A8:I5000").HorizontalAlignment = xlCenter
    Range("B8:B5000").VerticalAlignment = xlCenter
    Range("B8:B5000").Font.Size = 12
    
    End Sub

Do you see why or did i miss something ?

Thanks

 

[CSmith]

Why are you doing the entire range like that? I would think easier would be to only tweak the target ?

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .Column = 7 And .Row > 9 Then
      If .Cells.CountLarge > 1 Then Exit Sub
      Application.EnableEvents False
      If .Value = "" Then Target.Interior.Color = vbRed
      .Font.Size = 12
      .Font.Bold = True
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlCenter
      .Font.Size = 12
      Application.EnableEvents True
      End If
  End With
End Sub

 

[daniellouw]

This might be applicable to your problem: To keep the original cell format before you have inserted the hyperlink, use the following code:

Set A = Activesheet
myAddress = "'" & A.Name & "'!" & "A1"
With A.Cells(r, c)
    myPattern = .Interior.Pattern
    myIndex = .Font.ColorIndex
    myBold = .Font.Bold
    myItalic = .Font.Italic
    myUnderline = .Font.Underline
    
    A.Hyperlinks.Add Anchor:=A.Cells(r, c), Address:="", SubAddress:=myAddress

    .Interior.Pattern = myPattern
    .Font.ColorIndex = myIndex
    .Font.Bold = myBold
    .Font.Italic = myItalic
    .Font.Underline = myUnderline
End With