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
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