PDA

View Full Version : For/Next and custom formatting



phrankndonna
03-31-2012, 09:42 PM
Hi, this is a 2 part question.

1) For/Next. Why would the code below returning a 'Next without For' error message?

Sub Macro1()
Set Rng = Selection
For Each c In Rng
If c.Value Like "*a*" Then
c.Interior.ColorIndex = 6
c.NumberFormat = "+#,##0;-#,##0""*"""
Else
If c.Value Like "*b*" Then
c.Interior.ColorIndex = 7
c.NumberFormat = "+#,##0;-#,##0""**"""
Else: c.NumberFormat = "+#,##0;-#,##0"
Next

2) Custom formatting. Actual values in the cells may be such like 2.0113246 or -3.5322451; and when I custom format the range of cells being acted upon by the code above, it does give me values such as +3* and -2**. However, it doesn't do this for all the cells. Anything that ends up being +0 or +1 will not take the asterisks (e.g., +0 vice +0* or +1 vice +1**). Why would that be?

The code in #1 above is supposed to format the value in the cell to be the rounded whole number (with the + or - sign) with one or two asterisks concatenated, depending on the color of the cell.

This seems to be so close to working, but just not quite there yet. I hope someone can help me figure out what is going on. Thanks.

Frank

GTO
04-01-2012, 12:32 AM
Greetings,

Only to #1, try:
Sub Macro1()
Dim Rng As Range, c As Range

Set Rng = Selection

For Each c In Rng
If c.Value Like "*a*" Then
c.Interior.ColorIndex = 6
c.NumberFormat = "+#,##0;-#,##0""*"""
ElseIf c.Value Like "*b*" Then
c.Interior.ColorIndex = 7
c.NumberFormat = "+#,##0;-#,##0""**"""
Else
c.NumberFormat = "+#,##0;-#,##0"
End If
Next
End Sub

phrankndonna
04-01-2012, 07:53 AM
Hi,

Thank you for the reply. That worked. Those IF/ElseIf loops seem to be very picky about where things are placed.

As for the custom formatting, I figured that out. I had the asterisk concatenated to the custom formatting IF the resulting value was less than zero, but not if it was greater than zero. Below is the before and after.

Before: "+#,##0;-#,##0""*"""
After: "+#,##0""*"";-#,##0""*"""

It all works well now. Thank you for your time and assistance.

Frank

Paul_Hossler
04-01-2012, 08:00 AM
c.NumberFormat = "+#,##0;-#,##0"


.NumberFormat has 4 sections: Pos;Neg;Zero:Text

If the Zero section is not specified, it uses the Pos part. Not sure I'm understanding how you want 0's formatted, but in the ZZZZ part below is where you'd probably specify the format string


c.NumberFormat = "+#,##0;-#,##0;ZZZZ"



If 1's are mis-formatted, it might be a rounding issue where Excel trys to help you



Paul

phrankndonna
04-07-2012, 07:04 AM
Hi, the original c.NumberFormat = "+#,##0;-#,##0" worked fine, and I was trying to find a way to concatenate an asterisk. It works beautifully with "+#,##0""*"";-#,##0""*""". Thanks for following up!

Frank