number formatting based on cell value

[Guest]

Hi
I would like to format a range of numbers in a spreadsheet depending on
their individual values. The cell values are linked (e.g. ='Totals in
solution'!E7) to a cell containing an 'if' statement and calculation e.g.
=IF(D7<(D$3*D$4),((-D$3*D$4)*$C7/$B7),D7*($C7/$B7)) on another worksheet
within the workbook. It kicks out values ranging from -30 to >100 which I'd
like to format to 3 sig figs if less than 100 and no d.p. if 100 or greater.
I'd also like to be able to format the negative values to <0.000, <0.00 and
<00.0. I've a custom format that can manage some of this but not all. Any
suggestions greatly appreciated.

 

[Guest]

Looks like you have 5 different formats based on value.
custom format can handle positiver nad negative so that take care of 2
set custom format to the most likely positive and negative formats (not
necessary but good practice)

use Format>conditionalFormat to assign 3 additional formats if "cell is"
"between" teh 3 other ranges. you are allowed 3 conditional formats per cell

 

[Guest]

Mnay thanks. This is done manually at the mo by applying the conditional
formatting throughout then manually picking cells for the negative and >100
value format but its prone to cells getting missed. Is there a way I could
automate with a search and replace function perhaps?

 

[Ron Rosenfeld]

Hi
I would like to format a range of numbers in a spreadsheet depending on
their individual values. The cell values are linked (e.g. ='Totals in
solution'!E7) to a cell containing an 'if' statement and calculation e.g.
=IF(D7<(D$3*D$4),((-D$3*D$4)*$C7/$B7),D7*($C7/$B7)) on another worksheet
within the workbook. It kicks out values ranging from -30 to >100 which I'd
like to format to 3 sig figs if less than 100 and no d.p. if 100 or greater.
I'd also like to be able to format the negative values to <0.000, <0.00 and
<00.0. I've a custom format that can manage some of this but not all. Any
suggestions greatly appreciated.

You can use an event driven macro (Sheet_change). However, given your
description there are some uncertainties as to which cells, when they change,
should trigger the macro. The variable "Target" gets set to the cell that
changes when the macro "fires". However, that will be the source cell of your
calculations.

For testing purposes, you can see what I set Source equal to, but you should
restrict it to the minimum range required.

One might think about using the dependents property of Target. Unfortunately,
that will only apply to the active sheet, so may not add to the efficiency.

Perhaps someone else knows a better method to restrict the firing of this
macro, but it should work.

To enter it, right click on the sheet tab "Totals in Solution"; select the View
Code option, and paste the code below into the window that opens.

Alter the code so that AOI refers to the range where the data you wish to have
formatted is being displayed; and Source refers to the area where you enter
data that will change the results in AOI.

Hope this helps.

==================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range, Source As Range, c As Range

Set AOI = Worksheets("Sheet1").Range("B220")
'Edit to range where results displayed
Set Source = Worksheets("Totals in solution").Range("A1:E50")
'Edit to range where data is entered

If Not Intersect(Target, Source) Is Nothing Then
On Error GoTo Handler
For Each c In AOI
With c
'round to 3 significant digits only for testing
Select Case Application.WorksheetFunction.Round _
(.Value, Fix(-Log(Abs(.Value)) / Log(10)) + _
3 + (Abs(.Value) > 1))
Case Is >= 100
.NumberFormat = "0"
Case Is >= 10
.NumberFormat = "0.0"
Case Is >= 1
.NumberFormat = "0.00"
Case Is > -1
.NumberFormat = "0.000;-0.000;0.000"
Case Is > -10
.NumberFormat = ";-0.00;"
Case Else
.NumberFormat = ";-0.0;"
End Select
End With
Next c
End If
Exit Sub

Handler: Select Case Err.Number
Case Is = 13 'Type Mismatch Probably a Text entry
Resume Next
Case Is = 5 'Invalid procedure call; .value probably 0
If c.Value = 0 Then c.NumberFormat = "0.000"
Resume Next
Case Else
MsgBox "Error " & Err.Number & " " & Err.Description
Resume Next
End Select

End Sub
=============================


--ron

 

[Guest]

no manual work in my earlier post

to be precise:
put your formula in all cells required then:

format all cells with 3 dp ##0.000

then select all cells and format>conditional format
(Excel allows up to 3 conditional formats)
cell is > 100 format ##0
cell is between 0 and -10 format ##0.00
cell is between -10 and -20 format ##0.0

the cells will automatically format the decimal places based on the value
derived from the formula..

 

[Ron Rosenfeld]

no manual work in my earlier post

to be precise:
put your formula in all cells required then:

format all cells with 3 dp ##0.000

then select all cells and format>conditional format
(Excel allows up to 3 conditional formats)
cell is > 100 format ##0
cell is between 0 and -10 format ##0.00
cell is between -10 and -20 format ##0.0

the cells will automatically format the decimal places based on the value
derived from the formula..

What version of Excel are you using?

In Excel 2002, conditional formatting only has options for formatting the Font;
Borders and Patterns.


--ron