Excel自动点击超链接背景颜色修改问题及VBA代码求助

Let's break down why your current code isn't behaving as expected, then fix it to match the logic you outlined:

What's wrong with your existing code? #

• Your error handling is totally disconnected from the hyperlink check: That 1/i trick to trigger an error has nothing to do with validating if a hyperlink works, and you’re trying to reference links.Parent before the links variable even points to an actual hyperlink.
• The loop over hyperlinks is stuck inside the wrong conditional block, so it never runs correctly under normal circumstances.
• You’re not resetting error handling properly, which can hide real issues and cause unexpected behavior.

Revised Code (Matches Your Intended Logic) #

Sub ValidateAndColorLinks()
    Dim targetLink As Hyperlink
    Dim targetRange As Range
    
    ' Set your target range (update "Range1" to your actual named range or cell range)
    Set targetRange = Range("Range1")
    
    ' Loop through every hyperlink in the specified range
    For Each targetLink In targetRange.Hyperlinks
        On Error Resume Next ' Turn on error handling to catch broken links
        ' Try to follow the link (NewWindow=False reduces screen flicker)
        targetLink.Follow NewWindow:=False, AddHistory:=False
        
        ' Check if an error occurred (link is invalid)
        If Err.Number <> 0 Then
            ' Color the cell background for invalid links (adjust RGB as needed)
            targetLink.Parent.Interior.Color = RGB(255, 204, 204) ' Light red
        Else
            ' Optional: Uncomment below to color valid links too
            ' targetLink.Parent.Interior.Color = RGB(204, 255, 204) ' Light green
        End If
        
        On Error GoTo 0 ' Reset error handling to default
        Err.Clear ' Clear any leftover error flags for the next check
    Next targetLink
End Sub

What Changed & Why? #

• Direct hyperlink loop: We iterate directly over each hyperlink in your target range, which aligns perfectly with your goal of checking every link.
• Targeted error handling: We only enable error handling right before trying to follow the link, so any error (like a broken URL) is caught immediately.
• Clean error reset: After each check, we reset error handling and clear the error flag to make sure subsequent links are checked correctly.
• Less flicker: The NewWindow:=False parameter stops the link from opening a new window, which cuts down on that annoying screen flash you saw.

Quick Customization Tips #

• Tweak the RGB() values to use your preferred colors (e.g., RGB(255, 255, 153) for yellow).
• If you don’t want to actually open the link (just validate it), you could use URL-checking API calls, but for most use cases, the Follow method with error handling is simple and effective.

内容的提问来源于stack exchange，提问作者Badja