This is probably "stupid simple" but i'm struggling anyway

[Mr_Ragweed]

Hello. I'm trying to write a simple macro that loops through each row a data on a single sheet. If the value in Column C is "Percent" i need to select range "D:M" on that row and convert the number format to percent. If the value in Column C is "Dollars" in need to select the range and convert the number format to dollars.
My code is below:

Sheets("ProductFormulas").Select
ActiveSheet.Range("D:M").Name = "displays"
For I = 2 To NewFinalRow
    If ActiveSheet.Cells(NewFinalRow, 3).Value = "Percent" Then
      ActiveSheet.Range("displays").Select
      Selection.NumberFormat = "0.00%"
        ElseIf ActiveSheet.Cells(NewFinalRow, 3).Value = "Dollars" Then
        ActiveSheet.Range("displays").Select
        Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    End If
Next I

The problem is the enire columns D:M are being formatted as one instead of formatting each row.

Any help is much appreciated.Thank You.

 

[Joe4]

If the value in Column C is "Percent" i need to select range "D:M" on that row and convert the number format to percent. If the value in Column C is "Dollars" in need to select the range and convert the number format to dollars.

There is a much easier way to do this using no VBA code. Just use Conditional Formatting (it is designed to do things like this!)

 

[Mr_Ragweed]

Great point Joe4!
I dont think i can in this instance however. This is part of a much larger macro that involves userinterfaces. I need to run this on the "NewFinalRow" each time a row is added to a results sheet.
I see that my original post phrased it as if to run on a whole sheet. Running on the whole results sheet each time is less than efficient, but in this case i need effective more than efficient.
Therfore:
Option #1: Format the new row after it is copied to the result sheet
Option #2: Format each row on the result sheet each time a new row is added

The result sheet is in the background anyway until the user is finshed, so I will turn off the screen updating to help speed it along if option #2 is the route that gets taken.

Thanks!

 

[Joe4]

I would think Option 1 would be preferable, as it is very inefficient to run your code on all rows multiple times unnecessarily.

Here is a block of code that will format the last row (based on the entry in column C). So if you could run this each time a new row is added, it should do what you want.

    Dim myLastRow As Long
    
    'Find last added row but looking for last row in column C with entry
    myLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    'Determine how to format columns D-M based on entry in column C
    Select Case Cells(myLastRow, "C")
        Case "Percent"
            Range(Cells(myLastRow, "D"), Cells(myLastRow, "M")).NumberFormat = "0.00%"
        Case "Dollars"
            Range(Cells(myLastRow, "D"), Cells(myLastRow, "M")).NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    End Select

 

[Mr_Ragweed]

Ohhhhhhhhhhhhhhhhhhhhhh, that's just beautiful. It works like a dream!!!! I see now how i was messing up my range - i'm always so dang close but "close means zero in VBA" is something else i've learned.
And you dont even have to remember to do an 'End If'. I may try and adapt "case" to some other parts of this project - or at least will certainly remember it for the future!


Thank you fine sir!

 

[Joe4]

You're most welcome!

Select Case is nice to use when you have more than one value you are checking for. You can do it with multiple or nested IF statements, but they can get a bit messy and tough to read if you have more than one or two.

 

[Mr_Ragweed]

Yes i see it now. I do have some lengthy nested If's elsewhere in the project (maybe 10 nests in an If). They all function properly so i hate to go and mess up a good thing - but thats why you save multiple versions.

To play off of your quote signature - I just learned to be a better fisherman!
Thanks