Macro Error

Discussion in 'Excel Programming' started by Gaura215, Apr 15, 2014.

  1. Gaura215

    Gaura215 Guest

    Hi All

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

    For Each cell In Range("D10:AX67")
    If cell.Value = "a" Then
    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 cel
    Gaura215, Apr 15, 2014
    1. Advertisements

  2. Gaura215

    Claus Busch Guest


    Am Tue, 15 Apr 2014 09:47:36 +0100 schrieb Gaura215:
    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
    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

    Claus B.
    Claus Busch, Apr 15, 2014
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.