Auto Fill Cells

[Ed O'Brien]

I posted this earlier but for some reason cannot see it.


Excel 97/2003 & 2007

I have a sheet which is completed daily by a user.

The rows number about 30 cells (entries) and columns relate to the days of
the month. The day is enumerated in the first column 1 thro 31 (say).
So, to picture it, you have a blank chart with the date listed down the left
and columns named across the top.

Does anyone know of a formula to colour fill the date cell (highlight) if a
number is entered in any of the cells in that row?

TIA for any help.

Ed

 

[pogiman via OfficeKB.com]

Conditional formatting would do the trick...

Select one of the cell you want the format to appear then enter this on
conditional formatting selecting "The formula is":

=len(Current Cell Address)>0 -> create the format you want. This will
recognize either text or value.

Hope this helps.

 

[Ed O'Brien]

Hi,

Sorry to be slow getting back - sleeping!

I'm afraid I have not been able to get this to work.

I am using Excel 2007 in Office Basic. I can't find the words "The formula
is" within Conditional Formatting.

The chart I am working with contains 39 rows and 45 columns....

To help me understand a bit better ( I'm not too bright with Excel) say cell
B5 is the cell I want to highlight (fill) in Red immediately any cell from
C5 to AS5 has data entered. (Number).

Thanks for your help and patience.

Ed

 

[Gord Dibben]

Ed

The actual syntax in the New Rule dialog is

"use a formula to determine which cells to format"

Last in the dialog box.


Gord Dibben MS Excel MVP

 

[Ed O'Brien]

Hi, Gord. Thanks for coming in on this...

I don't think I understand what pogiman means by "...create the format you
want."

If I want cell B5 to highlight when, say, a number is typed into cell F5, I
read it as meaning I select B5, go to Conditional Formatting and select the
New Rule, like you said. - But then what? I tried entering the formula
=len(B5)>0 -> but got a notice offering the usual advice to go to
Help!

Can you shed any more light on this for me?

So you don't think I'm being rude - this is pretty important to me - but
I'll be a while responding. I should be back in about 8 hours.

Ed

 

[Gord Dibben]

Select B5 and CF>New Rule>"use a formula to determine which cells to format"

=LEN(F5)>0

Ignore the -> pogiman used it just as an arrow to next step.

Format to a nice fill color.

When F5 has anything in it, B5 will be filled.

To your original request to fill B5 if any cell from C5 to AS5 has
data...........

Use a formula of =COUNTA(C5:AS5)>0


Gord

Hi, Gord. Thanks for coming in on this...

I don't think I understand what pogiman means by "...create the format you
want."

If I want cell B5 to highlight when, say, a number is typed into cell F5, I
read it as meaning I select B5, go to Conditional Formatting and select the
New Rule, like you said. - But then what? I tried entering the formula
=len(B5)>0 -> but got a notice offering the usual advice to go to
Help!

Can you shed any more light on this for me?

So you don't think I'm being rude - this is pretty important to me - but
I'll be a while responding. I should be back in about 8 hours.

Ed

 

[Ed O'Brien]

Brilliant, Gord. Worked fine. Many thanks.

Ed