Dear Experts:

I would like to achieve the following using a macro:

I got a workbook with several sheets. Most of the cells of these
worksheets contain hyperlink addresses.

The requirements for the macro are as follows:

Format all cells of all the worksheets with a grey fill where the
hyperlink address contains the string '%20'.

I am referring to the actual hyperlink address NOT the displayed
name.

Help is much appreciated. Thank you very much in advance.

On Aug 2, 3:30*am, andreashermle wrote:
Dear Experts:

I would like to achieve the following using a macro:

I got a workbook with several sheets. Most of the cells of these
worksheets contain hyperlink addresses.

The requirements for the macro are as follows:

Format all cells of all the worksheets with a grey fill where the
hyperlink address contains the string '%20'.

I am referring to the actual hyperlink address NOT the displayed
name.

Help is much appreciated. Thank you very much in advance.

Sub HyperlinksInteriorColorIndexSAS()
Dim i As Long
Dim H As Hyperlink
For i = 1 To Worksheets.Count
For Each H In Sheets(i).Hyperlinks
If InStr(H.Address, "%20") Then
. MsgBox H.Range.Address
Sheets(i).Range(H.Range.Address).Interior.ColorInd ex = 15
End If
Next H
Next i
End Sub

On Aug 2, 3:30*am, andreashermle wrote:
Dear Experts:

I would like to achieve the following using a macro:

I got a workbook with several sheets. Most of the cells of these
worksheets contain hyperlink addresses.

The requirements for the macro are as follows:

Format all cells of all the worksheets with a grey fill where the
hyperlink address contains the string '%20'.

I am referring to the actual hyperlink address NOT the displayed
name.

Help is much appreciated. Thank you very much in advance.

Sub HyperlinksInteriorColorIndexSAS()
Dim i As Long
Dim H As Hyperlink
For i = 1 To Worksheets.Count
For Each H In Sheets(i).Hyperlinks
If InStr(H.Address, "%20") Then
MsgBox H.Range.Address
Sheets(i).Range(H.Range.Address).Interior.ColorInd ex = 4
End If
Next H
Next i
End Sub

On 2 Aug., 15:22, Don Guillett Excel MVP
wrote:
On Aug 2, 3:30*am, andreashermle wrote:





Dear Experts:

I would like to achieve the following using a macro:

I got a workbook with several sheets. Most of the cells of these
worksheets contain hyperlink addresses.

The requirements for the macro are as follows:

Format all cells of all the worksheets with a grey fill where the
hyperlink address contains the string '%20'.

I am referring to the actual hyperlink address NOT the displayed
name.

Help is much appreciated. Thank you very much in advance.

Sub HyperlinksInteriorColorIndexSAS()
Dim i As Long
Dim H As Hyperlink
*For i = 1 To Worksheets.Count
*For Each H In Sheets(i).Hyperlinks
*If InStr(H.Address, "%20") Then
* MsgBox H.Range.Address
* Sheets(i).Range(H.Range.Address).Interior.ColorInd ex = 4
*End If
Next H
Next i
End Sub- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Don,

Great, exactly what I wanted. Thank you very much for your
professinal help.

Regards, Andreas

On Aug 3, 1:11*am, andreashermle wrote:
On 2 Aug., 15:22, Don Guillett Excel MVP
wrote:





On Aug 2, 3:30*am, andreashermle wrote:

Dear Experts:

I would like to achieve the following using a macro:

I got a workbook with several sheets. Most of the cells of these
worksheets contain hyperlink addresses.

The requirements for the macro are as follows:

Format all cells of all the worksheets with a grey fill where the
hyperlink address contains the string '%20'.

I am referring to the actual hyperlink address NOT the displayed
name.

Help is much appreciated. Thank you very much in advance.

Sub HyperlinksInteriorColorIndexSAS()
Dim i As Long
Dim H As Hyperlink
*For i = 1 To Worksheets.Count
*For Each H In Sheets(i).Hyperlinks
*If InStr(H.Address, "%20") Then
* MsgBox H.Range.Address
* Sheets(i).Range(H.Range.Address).Interior.ColorInd ex = 4
*End If
Next H
Next i
End Sub- Zitierten Text ausblenden -

- Zitierten Text anzeigen -

Hi Don,

Great, exactly what I wanted. *Thank you very much for your
professinal help.

Regards, Andreas- Hide quoted text -

- Show quoted text -

Glad to help