Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional coloring of cells in VBA FOR DYNAMIC ROWS
i want to write in VBA teh conditional formatting with the following pseudo
code inside a macro : if slippage= 0 then color= green else if slippage <0 then color = blue else if slippage 0 color = red when any value<0 in AO column for any row then that cell should be in color green... similarly for <0 and =0 also here the slippage column is the column name AO. the rows in this sheet keeps adding up. so i cannot give specific range... the slippage is in column AO and today theer are 50 rows and topmor 2 rows can be added etc. so it should be general;. please help. rows are dynamic |
#2
|
|||
|
|||
Hi, Sorry if I am being a bit naive here, but why do you need VBA to format the column A0? could you not simply use a conditional formula on Column A0 that points to the rows in question?
Garry Quote:
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional coloring of cells in VBA FOR DYNAMIC ROWS
But how do i do taht for a column for which rows are dynamic.. im acually
doing an automation here.. i recoreded a macro for conditional formatting and the code is as below . But i do not have the column name here as AO. how will this code run on my column AO Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 41 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="0" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 10 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="0" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With "Garrystone" wrote: Hi, Sorry if I am being a bit naive here, but why do you need VBA to format the column A0? could you not simply use a conditional formula on Column A0 that points to the rows in question? Garry 'deepika :excel help[_2_ Wrote: ;619081']i want to write in VBA teh conditional formatting with the following pseudo code inside a macro : if slippage= 0 then color= green else if slippage <0 then color = blue else if slippage 0 color = red when any value<0 in AO column for any row then that cell should be in color green... similarly for <0 and =0 also here the slippage column is the column name AO. the rows in this sheet keeps adding up. so i cannot give specific range... the slippage is in column AO and today theer are 50 rows and topmor 2 rows can be added etc. so it should be general;. please help. rows are dynamic -- Garrystone |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional coloring of cells in VBA FOR DYNAMIC ROWS
Sorry for the below post. It works but how do i supress the format fora cell
which l is blank. In other words when there is no entry for a cell then it should not display any color and should be white only. My code displays green color for blank values also "deepika :excel help" wrote: But how do i do taht for a column for which rows are dynamic.. im acually doing an automation here.. i recoreded a macro for conditional formatting and the code is as below . But i do not have the column name here as AO. how will this code run on my column AO Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 41 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="0" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 10 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="0" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With "Garrystone" wrote: Hi, Sorry if I am being a bit naive here, but why do you need VBA to format the column A0? could you not simply use a conditional formula on Column A0 that points to the rows in question? Garry 'deepika :excel help[_2_ Wrote: ;619081']i want to write in VBA teh conditional formatting with the following pseudo code inside a macro : if slippage= 0 then color= green else if slippage <0 then color = blue else if slippage 0 color = red when any value<0 in AO column for any row then that cell should be in color green... similarly for <0 and =0 also here the slippage column is the column name AO. the rows in this sheet keeps adding up. so i cannot give specific range... the slippage is in column AO and today theer are 50 rows and topmor 2 rows can be added etc. so it should be general;. please help. rows are dynamic -- Garrystone |
#5
|
|||
|
|||
Hi
I think you are just missing a select command, try this: Sub yourMacro_Name() Range("AO1:AO500").Select 'Where AO1:AO500 is the range in column AO that may be used for your conditions. Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="0" With Selection.FormatConditions(1).Font .Bold = True .Italic = False .ColorIndex = 41 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _ Formula1:="0" With Selection.FormatConditions(2).Font .Bold = True .Italic = False .ColorIndex = 10 End With Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="0" With Selection.FormatConditions(3).Font .Bold = True .Italic = False .ColorIndex = 3 End With End Sub Quote:
|
#6
|
|||
|
|||
If you want to return a blank in the AO column for those rows that have no data in them, then you could use a function on column AO. In the example below the 'if' function checks to see if cells A3, B3...G3 is blank (in this case these are the range that could contain data, yours may be dif.). If all the range is blank then a blank entry (" ") will show in the AO cell that refers to that row, otherwise the range A3:G3 is added together and is shown in that cell, regardless if the value =0 or not it will show.
=IF(AND(ISBLANK(A3),ISBLANK(B3),ISBLANK(C3),ISBLAN K(D3),ISBLANK(E3),ISBLANK(F3),ISBLANK(G3)),"",SUM( A3:G3)) I hope this is what you were asking P.s. you could also use a filter to show only the non blank rows using either a filter or creating a list. Garry Last edited by Garrystone : January 30th 08 at 01:56 PM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA - Coloring Rows Instead of Cells | Excel Discussion (Misc queries) | |||
Coloring rows of cells | Excel Discussion (Misc queries) | |||
Conditional coloring of Excel cells, based on adjacent cell values? | Excel Worksheet Functions | |||
Dynamic pie chart coloring | Excel Discussion (Misc queries) | |||
conditional coloring | Excel Worksheet Functions |