Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate a spreadsheet from a data set | Excel Worksheet Functions | |||
Formating a cell to auto populate | Setting up and Configuration of Excel | |||
Using a current spreadsheet to populate a new one | Excel Discussion (Misc queries) | |||
Install dates formating using conditional formating? | Excel Discussion (Misc queries) | |||
VB Form to populate spreadsheet. | Excel Programming |