change number format based on content of another column using vba

**Declamatory** · 12-17-2014, 06:31 AM

Hi Everyone,

I am trying to format cells as part of a larger macro that does various reporting, pivots etc. Because conditional format painter does not work in vba I am trying to get some coding that will do the following:

Column F has some cells blank and some cells containing text e.g. “Total GBP” or “Net GBP Deals”. Where the cell in column F contains the text “GBP” I would like to format the corresponding cell in column H in GBP currency format. Where column F contains “USD” I would like the corresponding cell in column H in USD currency format.

The number of lines with data will change every time the user runs the macro and there will be some rows that are blank in between data.

I'm pretty new to VBA so any help you can give me would be appreciated.

**Norie** · 12-17-2014, 06:48 AM

Why can't you apply the conditional formatting to the entire range in one go?

**Declamatory** · 12-17-2014, 07:06 AM

Hi Norie,

The conditional formatting works fine when recording the macro but when I try to run the macro I get a run time error. ExecuteExcel14Macro “2,1, I have posted previously about this problem which means the format painter doesn’t work. Nobody has been able to help me which is why I was looking for some VBA code instead. If you can find a solution to the format painter problem running in a macro that would be equally helpful. Thanks Dec

**Declamatory** · 12-17-2014, 07:09 AM

Am I way off with this? It doesn't work. I used an ifnumber(search function to state whether or not column F is GBP or USD and this is reflected in column P which is why the code is looking at the value of column P rather than column F.

 On Error GoTo ExitPoint
    Dim l As Long
    For l = 10 To 400
        With ActiveSheet.Cells(l, "H")
            If .Parent.Cells(l, "P").Value = "GBP" Then
               .NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
            End If
        End With
    Next l
With ActiveSheet.Cells(l, "H")
            If .Parent.Cells(l, "P").Value = "USD" Then
               .NumberFormat = "[$$-409]#,##0.00"
            End If
        End With
    Next l
ExitPoint:
End Sub

**Norie** · 12-17-2014, 07:19 AM

Sorry, I'm a little confused.

Did you have conditional formatting that was doing what you wanted?

As for the posted code, perhaps something like this, it's kind of close I think though the columns you use in the code don't tally with the columns you mention in the original post.

Anyway, this code formats column H based on the value in column P.

Dim cl As Range
Dim strFormat As String

    With ActiveSheet
        For Each cl In .Range("H10", .Range("H" & Rows.Count).End(xlUp)).Cells

            Select Case cl.Offset(,8).Value ' check value in column P
                Case "GBP" 
                    strFormat = "$#,##0.00_);[Red]($#,##0.00)"
                Case "USD" Then
                    strFormat = "[$$-409]#,##0.00"
            End Select

            cl.NumberFormat = strFormat

        Next cl
    End With

**Declamatory** · 12-17-2014, 10:51 AM

Hi Norie,

Yes I did change the columns (I did mention it above). The problem I had is that there are blank rows in my data. I had to insert another column and where there was a blank in column F put "GBP" in column P. This way all the cells in column but would be populated. It didn't matter if the blank cells in column H were formatted as GBP as there was nothing in those cells. Anyway I have been able to muddle my way through it with some online help from forums etc. This is what I got.

    introw = 5
 Do Until Range("P" & introw) = ""
    celltxt = Range("P" & introw).Text
    If InStr(1, UCase(celltxt), "GBP") Then
        Range("H" & introw).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    ElseIf InStr(1, UCase(celltxt), "USD") Then
        Range("H" & introw).NumberFormat = "[$$-409]#,##0.00_ ;[Red]-[$$-409]#,##0.00 "
    End If
    introw = introw + 1
 Loop

change number format based on content of another column using vba

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

change number format based on content of another column using vba

Re: change number format based on content of another column using vba

Re: change number format based on content of another column using vba

Re: change number format based on content of another column using vba

Re: change number format based on content of another column using vba

Re: change number format based on content of another column using vba

Thread Information

Users Browsing this Thread

Similar Threads

Change content of one cell based on content of another

[SOLVED] Change all matching Value based on number change in Column Excel 2007

Match Column A's Project number to change based on greater number in new row

Conditionally change number format based on another cell's value

change number format based on another cell?

Bookmarks

Bookmarks

Posting Permissions