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

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

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

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

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

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