Help Simplifying This Bit Of Code

[CaliKidd]

Hi, I am trying to consolidate lines of code, as follows:

First, an example:

I want this code:

With MySheet
   .Range("G4:G100").NumberFormat = "$#,##0.000"
   .Range("O4:P100").NumberFormat = "$#,##0.000"
End With

To look like this:
MySheet.Range("G4:G100,O4:P100").NumberFormat = "$#,##0.000"

And, to make a point later in this post, this is not the same as:
MySheet.Range("G4:G100","O4:P100").NumberFormat = "$#,##0.000"

However, I need to use a variable for the row number and, thus, need to use a concatenation string, as follows:

With MySheet
   .Range("G4:G" & MyVar).NumberFormat = "$#,##0.000"
   .Range("O4:P" & MyVar).NumberFormat = "$#,##0.000"
End With

When I try to consolidate the code (in the same way as above), it does not work correctly:

MySheet.Range("G4:G" & MyVar, "O4:P" & MyVar).NumberFormat = "$#,##0.000"

The problem appears to be with the apostrophes needed to concatenate the strings. It is compiling like the point above (with the red code), which is not what I want. I want it to compile like the green code, but removing the apostrophes isn't possible since I am concatenating the range as a string.

Can someone tell me what the correct format should be?

 

[VoG]

Try

MySheet.Range("G4:G" & MyVar & ",O4:P" & MyVar).NumberFormat = "$#,##0.000"

 

[CaliKidd]

Thanks, Peter. Worked great!