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.


    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 cel
    -------------------


    --
    Gaura215
     
    Gaura215, Apr 15, 2014
    #1
    1. Advertisements

  2. Gaura215

    Claus Busch Guest

    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
     
    Claus Busch, Apr 15, 2014
    #2
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Val Steed

    Macro Security Error No Macro

    Val Steed, Sep 10, 2003, in forum: Excel Programming
    Replies:
    1
    Views:
    85
    Ronald Dodge
    Sep 10, 2003
  2. Pete

    Start Macro / Stop Macro / Restart Macro

    Pete, Nov 21, 2003, in forum: Excel Programming
    Replies:
    2
    Views:
    217
    Tom Ogilvy
    Nov 21, 2003
  3. Neo
    Replies:
    0
    Views:
    153
  4. Anddmx
    Replies:
    6
    Views:
    215
    Anddmx
    Jun 9, 2004
  5. D
    Replies:
    3
    Views:
    152
    Otto Moehrbach
    Feb 28, 2005
  6. David
    Replies:
    9
    Views:
    407
    Steve D
    Feb 16, 2006
  7. Dricon1

    Compile error macro is 111K. how do I fix macro

    Dricon1, Feb 17, 2007, in forum: Excel Programming
    Replies:
    2
    Views:
    120
    gerdmain
    Feb 18, 2007
  8. Steven

    Macro Error stops App Macro

    Steven, Jan 6, 2009, in forum: Excel Programming
    Replies:
    2
    Views:
    98
Loading...