0

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

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

Thanks

Community
  • 1
  • 1
Maddy
  • 907
  • 3
  • 10
  • 25

3 Answers3

3

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
raybiss
  • 401
  • 2
  • 8
0

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

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

  2. On the Home section 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 Format and set Number to Currency, then click OK.

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

Andi Mohr
  • 458
  • 1
  • 6
  • 22
0

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