Please check my code for conditional formatting

C

Conan Kelly

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 Format>Conditional 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
 
P

Peter T

Hi Conan,

Your problems would disappear if your CF formulas contained only absolute
address's. But if you need relative simplest way based on your code -

Sub test()
Range("B30:B40").Select
Selection.FormatConditions.Delete
s$ = ActiveCell.Offset(0, 1).Resize(1, 243 - 2).Address(0, 0)
s$ = "=ROUND(SUM(" & s & "),2)<>0"
ActiveCell.FormatConditions.Add xlExpression, , s$
ActiveCell.AutoFill Selection, xlFillFormats
End Sub

This assumes it's OK for all cells to have same formats as B30
If not there are other ways.

Regards,
Peter T

Conan Kelly said:
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 Format>Conditional
Click to expand...
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
Click to expand...
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?
Click to expand...
I'm not telling it to skip every other one or use even
 
P

Peter T

I didn't notice your format, before the autofill -
ActiveCell.FormatConditions(1).Interior.ColorIndex = 3

Peter T
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Crosstab column headings for conditional formatting in report 0
Tough conditional formatting question 8
conditional formatting w/ check for beginning odd or even 4
AutoDetect the path of the file? 4
Macro to tidy data 4
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0

Top