Formula Conditional formatting on numbers

[Antonio]

I need to format a number in a cell depending on the contents of a different
cell.

Some cells will have two decimals, some none, depending on the contents of
their adjacent cells.

Conditional formatting. Formula Is. Allows me to change the font, border,
pattern etc. but not the number format.

Is it possible to do ?

Thanks,


Antonio

 

[Ardus Petus]

You can do that with a VBA event proc.

HTH

 

[Antonio]

Hi Ardus,

Which event would you use and how?

Are you thinking of the SheetChange event?

Thanks,

Antonio

 

[JB]

http://cjoint.com/?gehpMSd6M4


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B2:B1000], Target) Is Nothing And Target.Count = 1
Then
Application.EnableEvents = False
For Each c In Range("MesCouleurs")
If Target.Value > c.Value Then
c.Copy
Target.PasteSpecial Paste:=xlPasteFormats
End If
Next c
Application.EnableEvents = True
End If
End Sub

Cordialy JB


Antonio a écrit :

 

[Antonio]

Hi JB and Ardus,

Thank you again for your idea that works perfectly.

One question though, isn't worksheet_change routine very demanding on
computer resources and might slow down the sheet substantially (at least in
my case)?

I will test it tomorrow when the servers are up but since I have many cells
being updated every few split seconds with live market feeds, I wonder if
each update will call the worksheet_change event. Even though the vast
majority will not execute more than a couple of lines, this continuous
checking sounds two much additional computing work.


Regards,

Antonio

http://cjoint.com/?gehpMSd6M4


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B2:B1000], Target) Is Nothing And Target.Count = 1
Then
Application.EnableEvents = False
For Each c In Range("MesCouleurs")
If Target.Value > c.Value Then
c.Copy
Target.PasteSpecial Paste:=xlPasteFormats
End If
Next c
Application.EnableEvents = True
End If
End Sub

Cordialy JB


Antonio a écrit :

I need to format a number in a cell depending on the contents of a different
cell.

Some cells will have two decimals, some none, depending on the contents of
their adjacent cells.

Conditional formatting. Formula Is. Allows me to change the font, border,
pattern etc. but not the number format.

Is it possible to do ?

Thanks,


Antonio

 

[JB]

Resources for this programs are slow, less for conditional
formatting(there is no formule )

New version: http://cjoint.com/?getJyia8J7

Another sample:
http://www.excelabo.net/compteclic.php?nom=jb-barrecoloriage
http://www.excelabo.net/compteclic.php?nom=jb-planningmensuelmacro

Cordialy JB

Antonio a écrit :

Hi JB and Ardus,

Thank you again for your idea that works perfectly.

One question though, isn't worksheet_change routine very demanding on
computer resources and might slow down the sheet substantially (at least in
my case)?

I will test it tomorrow when the servers are up but since I have many cells
being updated every few split seconds with live market feeds, I wonder if
each update will call the worksheet_change event. Even though the vast
majority will not execute more than a couple of lines, this continuous
checking sounds two much additional computing work.


Regards,

Antonio

http://cjoint.com/?gehpMSd6M4


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([B2:B1000], Target) Is Nothing And Target.Count = 1
Then
Application.EnableEvents = False
For Each c In Range("MesCouleurs")
If Target.Value > c.Value Then
c.Copy
Target.PasteSpecial Paste:=xlPasteFormats
End If
Next c
Application.EnableEvents = True
End If
End Sub

Cordialy JB


Antonio a écrit :

I need to format a number in a cell depending on the contents of a different
cell.

Some cells will have two decimals, some none, depending on the contents of
their adjacent cells.

Conditional formatting. Formula Is. Allows me to change the font, border,
pattern etc. but not the number format.

Is it possible to do ?

Thanks,


Antonio