Solved

Excel VBA Format Text

Posted on 2014-04-07
3
361 Views
Last Modified: 2014-04-07
I need to format multiple Columns A,D,I,L Text on all worksheets except for 2 (Invoice and Summary).
0
Comment
Question by:shieldsco
  • 2
3 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39982937
Use this macro:
Sub Demo()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> "Invoice Sheet" And .Name <> "Summary Sheet" Then
                .Range("A:A").Interior.Color = vbRed    ' or whatever formatting you want
                .Range("D:D").Interior.Color = vbGreen    ' or whatever formatting you want
                .Range("I:I").Interior.Color = vbCyan    ' or whatever formatting you want
                .Range("L:L").Interior.Color = vbBlue    ' or whatever formatting you want
            End If
        End With
    Next

End Sub

Open in new window

0
 

Author Comment

by:shieldsco
ID: 39982963
I get the following runtime error 1004 method range of object _worksheet failed on  .Range("A").NumberFormat = "@"

Code:
Sub Demo()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> "Invoice Sheet" And .Name <> "Summary Sheet" Then
                .Range("A").NumberFormat = "@"
                .Range("D").NumberFormat = "@"
                .Range("L").NumberFormat = "@"
            End If
        End With
    Next

End Sub
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39982974
Sub Demo()

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .Name <> "Invoice Sheet" And .Name <> "Summary Sheet" Then
                .Range("A:A").NumberFormat = "@"
                .Range("D:D").NumberFormat = "@"
                .Range("I:I").NumberFormat = "@"
                .Range("L:L").NumberFormat = "@"
            End If
        End With
    Next

End Sub

Open in new window

0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now