Share via

How to stop Excel from automatically adopting the hyperlink format (underline + blue font) when I insert a new row adjacent to another cell that contains a hyperlink?

Anonymous
2023-05-30T15:52:41+00:00

I have a column of data in which some of the cells are hyperlinks and some are not. I only want the cells with hyperlinks to visually appear as hyperlinks (underline + blue font). The cells that don't contain a hyperlink should not look like they do -- they should appear in the default format. The problem is, when I insert a new row in the middle of the column, the cell will automatically be formatted as underlined + blue font REGARDLESS of whether the new cell contains a hyperilnk. This means I am unable to visually distinguish between cells that have a hyperlink and those that don't.

How can I fix this? I only want hyperlinked cells to look like they have a hyperlink.

If I change the format of the whole column, it removes the hyperlink formatting from the hyperlinked cells -- I don't want that.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-30T16:42:28+00:00

    Hi Larry

    I'm AnnaThomas and I'd happily help you with your question. In this Forum, we are Microsoft consumers just like yourself.

    To prevent this from happening, you can change the formatting of the new cell after inserting the row. Here’s how:

    Select the cell that is incorrectly formatted as a hyperlink. Right-click on the cell and select Format Cells from the context menu. In the Format Cells dialog box, go to the Font tab. Under Font style, select Regular. Under Color, select Automatic. Click on OK.

    This will remove the hyperlink formatting from the selected cell, while preserving the formatting of other cells in the column.

    Alternatively, you can use a macro to automatically remove the hyperlink formatting from new cells when you insert a row. Here’s an example of a macro that does this:

    Private Sub Worksheet_Change(ByVal Target As Range) ' Check if the change is an entire row being inserted If Target.Rows.Count = Me.Rows.Count Then ' Loop through each cell in the new row For Each cell In Target.Cells ' Check if the cell is formatted as a hyperlink If cell. Font.Underline = xlUnderlineStyleSingle And cell. Font.Color = RGB(5, 99, 193) Then ' Remove the hyperlink formatting cell. Font.Underline = xlUnderlineStyleNone cell. Font.Color = xlAutomatic End If Next cell End If End Sub

    I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

    Best Regards,

    AnnaThomas

    Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

    10+ people found this answer helpful.
    0 comments