vba Excel 使用宏更改列格式

Excel change column format using macro

提问by Maddy

If there is word COST in column name then change the column format to currency using macro for excel.

如果列名称中有 COST 一词，则使用 excel 宏将列格式更改为货币。

like if column name is - "Final Cost" or "Total Cost" or "Item Cost" then change column format to currency.

就像列名称是-“最终成本”或“总成本”或“项目成本”，然后将列格式更改为货币。

Thanks

谢谢

回答by raybiss

Try something like

尝试类似的东西

Public Sub FormatCostColumnsAsCurrency()

  Dim sht As Worksheet
  Set sht = ActiveSheet

  Dim rng As Range
  Dim i As Integer

  For i = 1 To sht.UsedRange.Columns.Count

      Set rng = sht.Cells(1, i)
      If InStr(LCase(rng.Text), "cost") > 0 Then
          sht.Columns(i).NumberFormat = "$#,##0.00"
      End If

  Next

End Sub

回答by raybiss

Try below code

试试下面的代码

Sub CurrFormat()
    Dim colHeader As Range
    Set colHeader = Range("A1:E1")

    Dim currCell As Range
    Set currCell = Cells.Find("COST")

    If Not currCell Is Nothing Then currCell.NumberFormat = "$#,##0.00"

End Sub

回答by Andi Mohr

You can do this with Conditional Formatting, no macro required.

您可以使用 执行此操作Conditional Formatting，不需要宏。

1. Highlight a cell in the left column of your table.

2. On the Homesection of the ribbon, click Conditional Formatting, then New Rule.

3. Use a formula to determine which cells to format.

4. Enter this formula. If your row headings don't start in A1, change accordingly: =SEARCH("cost",A$1,1)

5. Click Formatand set Numberto Currency, then click OK.

6. In the Conditional Formatting Rules Manager, set Applies toso that it covers your table.

1. 突出显示表格左列中的单元格。

2. 在功能区的Home部分上，单击Conditional Formatting，然后单击New Rule。

3. Use a formula to determine which cells to format.

4. 输入这个公式。如果您的行标题不是以 A1 开头，请相应更改：=SEARCH("cost",A$1,1)

5. 单击Format并设置Number为Currency，然后单击确定。

6. 在 中Conditional Formatting Rules Manager，设置Applies to使其覆盖您的桌子。