conditional coloring of cells in VBA FOR DYNAMIC ROWS

[deepika :excel help]

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, 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

;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

 

[deepika :excel help]

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

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

;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

 

[deepika :excel help]

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

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

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

;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

;619574']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

:
-

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





:
-

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-