populate conditional formating on a spreadsheet via VBA
 

Hi all,
I'm running macro on excel file an i need to build conditional
formatting to a large amount of excel,
does anyone have faster way to populate the conditional formatting
beside running 2 "for" in order to populate the data?

populate conditional formating on a spreadsheet via VBA
 

Hint: When programming try to record a macro on the excel spreadsheet to find
how excel would perform the task. I did this for conditional formatting and
got this code. It look like excel use range rather than the for. range may
be a quicker way of performing the operation.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/15/2007 by Joel Warburg
'

'
Range("H16:R48").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="13"
Selection.FormatConditions(1).Interior.ColorIndex = 45
End Sub


"thread" wrote:

Hi all,
I'm running macro on excel file an i need to build conditional
formatting to a large amount of excel,
does anyone have faster way to populate the conditional formatting
beside running 2 "for" in order to populate the data?

populate conditional formating on a spreadsheet via VBA
 

Hi Joel

I build something like this inside of the for statments

xlApp.Cells(row, Column).FormatConditions.Delete
xlApp.Cells(row, Column).FormatConditions.Add
Type:=xlExpression, Formula1:="=" & "$" &
ColumnLetter(xlApp.ActiveCell.Column + Column - 1) & "$" & row & "<"
& "$" & ColumnLetter(ActiveCell.Column + Column - 1 + LastColumn) &
"$" & row
xlApp.Cells(row,
Column).FormatConditions(1).Interior.ColorIndex = 3
do you think what you send me will be saficient enough when i need to
do the comparacy between cells?

Joel כתב:
Hint: When programming try to record a macro on the excel spreadsheet to find
how excel would perform the task. I did this for conditional formatting and
got this code. It look like excel use range rather than the for. range may
be a quicker way of performing the operation.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/15/2007 by Joel Warburg
'

'
Range("H16:R48").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="13"
Selection.FormatConditions(1).Interior.ColorIndex = 45
End Sub


"thread" wrote:

Hi all,
I'm running macro on excel file an i need to build conditional
formatting to a large amount of excel,
does anyone have faster way to populate the conditional formatting
beside running 2 "for" in order to populate the data?

populate conditional formating on a spreadsheet via VBA
 

thank you very much it was quite helpfull
thread כתב:
Hi Joel

I build something like this inside of the for statments

xlApp.Cells(row, Column).FormatConditions.Delete
xlApp.Cells(row, Column).FormatConditions.Add
Type:=xlExpression, Formula1:="=" & "$" &
ColumnLetter(xlApp.ActiveCell.Column + Column - 1) & "$" & row & "<"
& "$" & ColumnLetter(ActiveCell.Column + Column - 1 + LastColumn) &
"$" & row
xlApp.Cells(row,
Column).FormatConditions(1).Interior.ColorIndex = 3
do you think what you send me will be saficient enough when i need to
do the comparacy between cells?

Joel כתב:
Hint: When programming try to record a macro on the excel spreadsheet to find
how excel would perform the task. I did this for conditional formatting and
got this code. It look like excel use range rather than the for. range may
be a quicker way of performing the operation.

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/15/2007 by Joel Warburg
'

'
Range("H16:R48").Select
Selection.FormatConditions.Delete
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="2", Formula2:="13"
Selection.FormatConditions(1).Interior.ColorIndex = 45
End Sub


"thread" wrote:

Hi all,
I'm running macro on excel file an i need to build conditional
formatting to a large amount of excel,
does anyone have faster way to populate the conditional formatting
beside running 2 "for" in order to populate the data?