Prev: Add text to cliparts
Next: formula help
From: Peter on 24 Apr 2010 16:27
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

From: Dave Peterson on 24 Apr 2010 16:53
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>.



Peter wrote:
>
> 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
From: Pete_UK on 24 Apr 2010 16:56
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

On Apr 24, 9:27 pm, Peter <p...(a)talktalk.net> wrote:
> 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

From: p45cal on 24 Apr 2010 17:10

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




P
e
t
e
r
;
7
0
8
3
1
7

W
r
o
t
e
:


>
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


--
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=198238

http://www.thecodecage.com/forumz

From: Peter on 25 Apr 2010 06:31
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

On 24/04/2010 21:27, Peter wrote:
> 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
>
>


 | 
Pages: 1
Prev: Add text to cliparts
Next: formula help