Add currency to selected columns

[Apple08]

Hi All

I have to add the currency format e.g. £1234.56 to those columns with Cost as part of the column headings in row 3. Once the row 3 has a word 'Cost' in the heading then the whole column from row 4 onwards will be in currency format as £1234.56. Start from column to the last unknown column.

I have done the code below but it doesn't work:

Sub convertCurrency()

Dim i As Long
Dim c As Long
Dim Lastrow As Long
Dim LastCol As Long
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
LastCol = Range("A1").End(xlToRight)
For c = 2 To LastCol
If Cells(4, c).Value Like "*Cost*" Then
    Cells(i, c).NumberFormat = "£###0.00"
    
End If
Next c


End Sub

Please could anyone help?

 

[Apple08]

The currency format is now gone, all are in number format, I wonder is it because of the wild card *Cost is missing?

 

[Fluff]

How about

Sub convertCurrency()
   Dim c As Long
   Dim Lastrow As Long
   Dim LastCol As Long
   Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
   LastCol = Range("A3").End(xlToRight).Column
   For c = 2 To LastCol
      If LCase(Cells(3, c).Value) Like "*cost*" Then
          Range(Cells(4, c), Cells(Lastrow, c)).NumberFormat = "£###0.00"
      Else: Range(Cells(4, c), Cells(Lastrow, c)).NumberFormat = "###0.00"
      End If
   Next c
End Sub

 

[Apple08]

Thanks Fluff, it works perfectly!

 

[Fluff]

Glad we could help & thanks for the feedback