Hello all,

This is trippin' me out. I can't figure this out.

I'm trying to run this code on a set of cells (B30:B40):

Sub aaaaMacro1()
Dim prngCell As Range

Selection.FormatConditions.Delete

For Each prngCell In Selection
' Selection.FormatConditions.Add Type:=xlExpression, _
Formula1:="=ROUND(SUM(C30:II30),2)<0"
' prngCell.FormatConditions.Add xlExpression, , _
"=ROUND(SUM(" & Chr(prngCell.Column + 65) _
& prngCell.Row & ":II" And prngCell.Row & "),2)<0"
prngCell.FormatConditions.Add xlExpression, , _
"=ROUND(SUM(" & Chr(prngCell.Column + 65) _
& prngCell.Row & ":II" & prngCell.Row & "),2)<0"
' MsgBox prngCell.Address & vbCrLf & prngCell.FormatConditions(1).Formula1
With prngCell.FormatConditions(1).Font
.Bold = True
.Italic = False
.ColorIndex = 6
End With
prngCell.FormatConditions(1).Interior.ColorIndex = 3
Next prngCell
End Sub


My desired results is the following formulas in the conditional formatting of the coresponding cells:

Cell Address Conditional Formatting Formula
$B$30 =ROUND(SUM(C30:II30),2)<0
$B$31 =ROUND(SUM(C31:II31),2)<0
$B$32 =ROUND(SUM(C32:II32),2)<0
$B$33 =ROUND(SUM(C33:II33),2)<0
$B$34 =ROUND(SUM(C34:II34),2)<0
$B$35 =ROUND(SUM(C35:II35),2)<0
$B$36 =ROUND(SUM(C36:II36),2)<0
$B$37 =ROUND(SUM(C37:II37),2)<0
$B$38 =ROUND(SUM(C38:II38),2)<0
$B$39 =ROUND(SUM(C39:II39),2)<0
$B$40 =ROUND(SUM(C40:II40),2)<0


But my results are this (I verified this by clicking FormatConditional Formatting... on each cell AND the behavior of the
spreadsheet also verifies this):
NOTICE: The row reference in the formulas increase by 2 (only even numbered rows) while the actuall cell row only increases by 1

Cell Address Conditional Formatting Formula
$B$30 =ROUND(SUM(C30:II30),2)<0
$B$31 =ROUND(SUM(C32:II32),2)<0
$B$32 =ROUND(SUM(C34:II34),2)<0
$B$33 =ROUND(SUM(C36:II36),2)<0
$B$34 =ROUND(SUM(C38:II38),2)<0
$B$35 =ROUND(SUM(C40:II40),2)<0
$B$36 =ROUND(SUM(C42:II42),2)<0
$B$37 =ROUND(SUM(C44:II44),2)<0
$B$38 =ROUND(SUM(C46:II46),2)<0
$B$39 =ROUND(SUM(C48:II48),2)<0
$B$40 =ROUND(SUM(C50:II50),2)<0


THEN ON TOP OF IT ALL, if I run the following code:

Sub ReadFoldersFiles()
' Dim fso As New FileSystemObject
' Dim fsoFolder As Folder
' Dim fsoFolders As Folder
Dim prngCell As Range

' Set fsoFolder = fso.GetFolder("X:\----- (New)\2006_06_Data\Data Files\Not Imported")

Open "X:\----- (New)\2006_06_Data\CondFormattingFormulas.txt" For Output As #1

Write #1, "Cell Address", "Conditional Formatting Formula"

For Each prngCell In Selection
Write #1, prngCell.Address, "'" & prngCell.FormatConditions(1).Formula1
Next prngCell

Close #1
End Sub


THIS is the results I get in the resulting text file:

"Cell Address","Conditional Formatting Formula"
"$B$30","'=ROUND(SUM(C30:II30),2)<0"
"$B$31","'=ROUND(SUM(C31:II31),2)<0"
"$B$32","'=ROUND(SUM(C32:II32),2)<0"
"$B$33","'=ROUND(SUM(C33:II33),2)<0"
"$B$34","'=ROUND(SUM(C34:II34),2)<0"
"$B$35","'=ROUND(SUM(C35:II35),2)<0"
"$B$36","'=ROUND(SUM(C36:II36),2)<0"
"$B$37","'=ROUND(SUM(C37:II37),2)<0"
"$B$38","'=ROUND(SUM(C38:II38),2)<0"
"$B$39","'=ROUND(SUM(C39:II39),2)<0"
"$B$40","'=ROUND(SUM(C40:II40),2)<0"


WHAT THE HELL IS GOING ON HERE? I have my code set up correctly, don't I? I'm not telling it to skip every other one or use even
numbers only. How come it is entering one thing and telling me something different?

Thanks for any help anyone can provide,

Conan Kelly