Change cell colour, after hyperlink activated

[russelldt]

I have 3 cells (A1, A2 and A3) that have hyperlinked Mailto functions. When i have sent the email hyperlinked in cell A1, i want that cell to change fill colour from "No Color" to green. The change must be permanent so that it's visible that the email has been sent each time the file is opened. The other 2 cells should operate the same.

Thank you

 

[Candyman8019]

Give this a try. Add this sub to the sheet where your hyperlinks exist:
Private Sub Worksheet_selectionchange(ByVal Target As Range)

If Not Intersect(Range("A1:A3"), Target) Is Nothing Then

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub

 

[russelldt]

Give this a try. Add this sub to the sheet where your hyperlinks exist:
Private Sub Worksheet_selectionchange(ByVal Target As Range)

If Not Intersect(Range("A1:A3"), Target) Is Nothing Then

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub

Thanks, i will give it a shot.

 

[russelldt]

Thanks, i will give it a shot.

No luck, the cell fill does not change. I am using MAC, and this might be an issue.

 

[Joe4]

No luck, the cell fill does not change. I am using MAC, and this might be an issue.

Are you ABSOLUTELY sure that you placed this in the correct sheet module, and have VBA enabled?

The most common mistake that people make with Event Procedure VBA code is that they put it in the wrong module.
If you place it in the wrong place, it will NOT run automatically.

An easy way to make sure you have put it in the correct place is to right-click on the sheet tab name at the bottom of the worksheet, select "View Code", and paste the code in the VB Editor window that pops up.

 

[russelldt]

Are you ABSOLUTELY sure that you placed this in the correct sheet module, and have VBA enabled?

The most common mistake that people make with Event Procedure VBA code is that they put it in the wrong module.
If you place it in the wrong place, it will NOT run automatically.

An easy way to make sure you have put it in the correct place is to right-click on the sheet tab name at the bottom of the worksheet, select "View Code", and paste the code in the VB Editor window that pops up.

Ah...you are a genius, Thanks. can you tone down the colour to a lighter green ?

Thanks again

 

[Joe4]

Ah...you are a genius, Thanks. can you tone down the colour to a lighter green ?

Thanks again

You can pick whatever color you want.
Just turn on the Macro Recorder, and choose the color you want, and then stop the Macro Recorder.
Then view your recorded VBA code, and copy and paste the color code you recorded over the "5287936" Candyman provided in his code to you.

 

[russelldt]

You can pick whatever color you want.
Just turn on the Macro Recorder, and choose the color you want, and then stop the Macro Recorder.
Then view your recorded VBA code, and copy and paste the color code you recorded over the "5287936" Candyman provided in his code to you.

I have run into a problem. Here is my script, as you can see the range is columns AN and AO. Some of the cells are always blank (ie dont have a hyperlink). The problem i am having is that every cell i click in these two columns changes colour.

Private Sub Worksheet_selectionchange(ByVal Target As Range)

If Not Intersect(Range("An:ao"), Target) Is Nothing Then

With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5287936
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End Sub

 

[Joe4]

Try this:

Private Sub Worksheet_selectionchange(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Range("AN:AO"), Target)
    If rng Is Nothing Then Exit Sub
    
    For Each cell In rng
        If cell <> "" Then cell.Interior.Color = 5287936
    Next cell

End Sub

 

[russelldt]

Try this:

Private Sub Worksheet_selectionchange(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
    Set rng = Intersect(Range("AN:AO"), Target)
    If rng Is Nothing Then Exit Sub
   
    For Each cell In rng
        If cell <> "" Then cell.Interior.Color = 5287936
    Next cell

End Sub

Thanks Joe, that does the trick for me