ExcelBanter - Macro Error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Macro Error (https://www.excelbanter.com/excel-programming/450005-macro-error.html)

Hi All

I have written a code, which is working fine till it reaches the following script. Is their anything wrong I am doing here. I can confirm that the range I have defined does contain "a". Or is their any easier or alternate way to do it using VBA. please help.

Code:

For Each cell In Range("D10:AX67")

        If cell.Value = "a" Then

        cell.Select

    With Selection.Font

        .Name = "Webdings"

        .Size = 11

        .Strikethrough = False

        .Superscript = False

        .Subscript = False

        .OutlineFont = False

        .Shadow = False

        .Underline = xlUnderlineStyleNone

        .ColorIndex = xlAutomatic

        .TintAndShade = 0

        .ThemeFont = xlThemeFontNone

    End With

        

        End If

    Next cell

Hi,

Am Tue, 15 Apr 2014 09:47:36 +0100 schrieb Gaura215:

I have written a code, which is working fine till it reaches the
following script. Is their anything wrong I am doing here. I can confirm
that the range I have defined does contain "a". Or is their any easier
or alternate way to do it using VBA. please help.

did you try to run this code in an older version than 2007? Earlier
version can't work with TintAndShade.
Try following code. It works with all versions and is faster than
looping through the range:

Sub Test()
Dim c As Range
Dim FirstAddress As String

Set c = Range("D10:AX67").Find("a", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
With c.Font
.Name = "Webdings"
.Size = 11
.ColorIndex = xlAutomatic
End With
Set c = Range("D10:AX67").FindNext(c)
Loop While Not c Is Nothing And c.Address < FirstAddress
End If

End Sub

Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional