shading used cells only

[Guest]

i havea spread sheet document with over 25K entries spreading six rows. how
can i shade only the cells hat have numbers in them and leave the blank cells
with out shade with out doing it each numbered cell at a time. there has to
be a faster way

 

[Gord Dibben]

Select the range of cell. Assuming A1:F25000

Format>Conditional Formatting>Formula is.

=A1<>""

Format to a nice color and OK.


Gord Dibben MS Excel MVP

 

[Ragdyer]

[ 1 ] Are all the numbers the results of formulas,
OR
[ 2 ] Are all the numbers keyed in,
OR
[ 3 ] Both?

Hit <F5>, and click on "Special"

In the "Go To Special" window,

If the answer above is "1"
Click on "Formulas" then <OK>,

If the answer above is "2"
Click on "Constants", then <OK>,

Then, while the cells are *still* selected, format them to a color you like.

If the answer above was "3", then you'll just have to repeat the "Go To" and
format twice.

 

[Guest]

Select all of the cells (select the rows is probably quickest way) then use
Format | Conditional Formatting
in the first dialog that comes up, choose
[Cell Value Is]
Then choose [not between]
and enter 0 into both of the parameters so that the line reads:
Cell Value Is | not between | 0 | and | 0
sounds kind of lame, but keeps you from having to write a less than zero and
a greater than zero condition.
Then click the [Format...] button and the [Patterns] tab and choose the
color for your shading.

Click [OK] to make it permanent.

The only problem you'll have with this is that cells with zero in them will
not get shaded.

If your numbers never go outside of a certain range (say they are all
positive) then you can try other variations of Conditional Formatting to just
catch the ones you're interested in, including zero. For example, if numbers
are never negative, then you could choose the [greater than or equal to]
option and zero as the value.

 

[Gord Dibben]

Misread original.

Use =ISNUMBER(A1) for the CF>Formula is:


Gord Dibben MS Excel MVP