FOrmat cell

  • Thread starter Thread starter Kaushi
  • Start date Start date
K

Kaushi

HI,

I want to format excel cells in my workbook in away where if i enter
negative figures, cell colour or font colour becomes RED and for all
possitive figures it is green.

Can i kindly get some advice.

Thanks,
 
Kaushi

Format the area you are making your entries the green font color you like.
Then goto Format>cells>Number and select the red (1234.10) select the number
of decimal places you want and the thousands separator (,) if you want that.
This will put all negative numbers in red with (...). If you don't want the
parans you can now click on the custom catagory and remove them in the
"types" window.

Hope this helps

Mike Rogers
 
This can be done with custom formatting of cells.

If you don't understand how to create a custom number format read the
following site,

XL2003 How to create
http://office.microsoft.com/en-us/excel/HP051995001033.aspx?pid=CH010036911033
XL2007 How to create
http://office.microsoft.com/en-us/excel/HP012165031033.aspx?pid=CH100870371033

Once you understand how to create a custom format, keep reading.


The choice of colour readily accessed by name is limited [BLACK], [BLUE],
[CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW]. However, there is a
[COLOR n] option that opens up more choices.

Using GREEN, as below gives lime green and is difficult to read.

[Green]0;[Red]-0

Using [Color n], my opinion is the best available green shade is 10:

[Color10]0;[Red]-0

The above does nothing fancy in the way of formatting. Zero and positives
are green. Negative numbers are red. You may wish to use a different format
for your numbers (e.g. to pick of commas in thousands). The following will
do this:

[Color10]#,##0;[Red]-#,##0

This site will give you further help:
http://www.ozgrid.com/Excel/CustomFormats.htm


Run the following macro on a blank sheet to pick you n colour

Sub ListNColours()
'run in a blank sheet
On Error GoTo ListNColours_Exit
For i = 1 To 56
Range("A" & i) = i
Range("A" & i).Interior.ColorIndex = i

Next i

ListNColours_Exit:
End Sub
 
Back
Top