Is this possible?

[Peter]

Hello experts,

I have been trying to resolve the following:

In an (excel 2002)worksheet, column H contains a series of formulae eg:

In H3 is (H2+C3-B3) - it continues down to H500. Column A is formatted for date entry from
A1 to A500.

If A4 and the remainder have yet to have dates entered, I'm trying to change the font
colour of H4 (through to H5000) to white i.e. whilst the column is populated, the values
are not shown. I hope this makes sense.

Is this possible without using VBA and, if so, can anyone please help with the necessary
formula?

Regards

Peter

 

[Dave Peterson]

You could use format|conditional formatting to hide that value.

Or you could change the formula:
=if(count(h2,c3,b3)<3,"",(h2+c3-b3))

But if you want to try...
Select H3:Hxxx
and with H3 the activecell
Format|conditional formatting (xl2003 menus)
Formula is:
=count(h2,c3,b3)<3
And give the cell a white font color

I'd change the formula in the cell. Even though all 3 values are not entered,
you may find that the hidden value changes other cells that shouldn't be changed
until all 3 are entered.

And it can be a bear to find these magic values that are hidden with conditional
formatting <vbg>.

 

[Pete_UK]

Here's two ways:

a. Change your formula to this in H3:

=IF(OR(A3="",H2=""),"",H2+C3-B3)

and copy down.

b. Apply Conditonal Formatting. Highlight all the cells from H4 to
H5000, with H4 as the active cell. Click on Format | Conditonal
Formatting and in the dialogue box choose Formula Is rather than Cell
Value Is in the first box, and enter this formula in the next box:

=A4=""

Then click the Format button and choose white for your colour, then
click OK twice to exit the dialogue box. Excel will automatically
adjust the cell references for the highlighted cells.

Hope this helps.

Pete

 

[p45cal]

In H4, conditional formatting, Formula Is:
=A4=""
format white font, OK.
Copy H4, Select range H4:H500, Paste-Special|Formats, OK

 

[Peter]

Crikey - three replies within minutes, most impressive and thank you all.

I've tried p45cal's conditional suggestion as I need Column H to contain hidden figures
that are used elsewhere and, I believe it works.

More checking will follow - in the meantime,

Many thanks to Dave Peterson, p45cal & Pete_UK

Kind regards