ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Please check my code for conditional formatting (https://www.excelbanter.com/excel-programming/368578-please-check-my-code-conditional-formatting.html)

Conan Kelly
Please check my code for conditional formatting
 
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



Peter T
Please check my code for conditional formatting
 
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" <CTBarbarin at msn dot com wrote in message
...
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

Peter T
Please check my code for conditional formatting
 
I didn't notice your format, before the autofill -
ActiveCell.FormatConditions(1).Interior.ColorIndex = 3

Peter T

"Peter T" <peter_t@discussions wrote in message
...
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" <CTBarbarin at msn dot com wrote in message
...
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


All times are GMT +1. The time now is 06:41 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com