to follow a particular cell format

  • Thread starter Thread starter ronnieseah
  • Start date Start date
R

ronnieseah

e.g

i has set a conditional format for A1, the font will be red if th
conditional is met, or else it is black.

i want to set other cells to follow A1 format, e.g B2, C3 and D4 wil
follow A1 font color, how do i set it???
 
Select A1,B2,C3,D4, then with A1 as the active cell, enter the same condition as you did before but ensure the reference to A1 is absolute ($A$1)
 
thank for the reply.

still dont work.

A1 is today date,
B1 is contract starting date, C1 is contract ending date.
D1 is mode of payment, (which i type "unpaid" in cell)

i set a condition, if B1 greater then A1 (that mean contract ha
started), then i want the starting date to be in red font, this wor
fine.
when i follow your suggestion, C1 follow the font color as B1, but no
D1.
how can i set C1 and D1 to follow whatever font color B1 is having??
 
If A1 is working correctly, then just use the "FormatPainter" icon to do
your copying.

Click in A1.
*Double* click the "FormatPainter" icon (yellow paint brush),
And simply click in B2, C3, and D4.

*THEN*, hit <Esc> to end the format copy session.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

thank for the reply.

still dont work.

A1 is today date,
B1 is contract starting date, C1 is contract ending date.
D1 is mode of payment, (which i type "unpaid" in cell)

i set a condition, if B1 greater then A1 (that mean contract had
started), then i want the starting date to be in red font, this work
fine.
when i follow your suggestion, C1 follow the font color as B1, but not
D1.
how can i set C1 and D1 to follow whatever font color B1 is having???
 
thank ragdyer,

but still dont work because if today date is inbetween B1 and C1, tha
mean contract start, but havent end yet, B1 will be in red, but C1 wil
not be in red.
and D1 which is text will still remain the same color
 
Sorry, I completely misunderstood you.
Don't know how you exactly set the condition for B1, so I can't say copy it.
So, select C1 and set this condition:
Set first window to "Formula Is",
Enter in second window:
=$B$1>$A$1
And set format to red, then <OK>.

NOW, *single* click on the format painter icon, and simply click in D1.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



thank ragdyer,

but still dont work because if today date is inbetween B1 and C1, that
mean contract start, but havent end yet, B1 will be in red, but C1 will
not be in red.
and D1 which is text will still remain the same color.
 
thank RagDyer for being so patient.

this still cant work, because i want the font color of C1 to follow B1
no matter what.
for the suggestion that you just gave, B1 will be red and C1 will no
be red if today date fall inbetween B1 and C1.
and i cant get D1 font color to follow B1 because D1 is text whereas B
is date.
 
If I understand what you're saying, A1 is today's date.
B1 is to be red when, according to your own words, B1 is *greater* then A1.
BUT ... if today's date is constantly updated to "Today", then B1 will *no
longer* be *greater* then A1, so B1 will *no longer* be red!
It will revert back to normal (black).

Do I understand your situation correctly?

Would you post the *exact* formula (condition) that you set for B1, that you
said is working properly.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------



thank RagDyer for being so patient.

this still cant work, because i want the font color of C1 to follow B1,
no matter what.
for the suggestion that you just gave, B1 will be red and C1 will not
be red if today date fall inbetween B1 and C1.
and i cant get D1 font color to follow B1 because D1 is text whereas B1
is date.
 
ok, customer sign up with my company for a service contract, the servic
have a start date(B1) and end date (C1), usually service contrac
duration is 1 months.
today date is (A1), status (D1) which is text "unpaid"

I format B1 color to be red if B1 greater or equal to A1, which mea
contract had started.
the problem lie with C1, because i want C1 color to follow B1 but
cant use the same formula because TODAY might be in the middle of th
contract, which mean contract not ended yet, so C1 is smaller then A1
so it wont turn red.
I want C1 to follow B1 color, no matter when is the date. and i als
want the D1 text "unpaid" color to follow the same as B1.
 
We're still having a communication problem.

You JUST said that:
<<"TODAY might be in the middle of the contract, which mean contract not
ended yet, so C1 is smaller then A1, so it wont turn red.">>

Doesn't this *ALSO* mean that B1 is also smaller then A1, therefore B1 will
*not* be red *also*?
--
Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

ok, customer sign up with my company for a service contract, the service
have a start date(B1) and end date (C1), usually service contract
duration is 1 months.
today date is (A1), status (D1) which is text "unpaid"

I format B1 color to be red if B1 greater or equal to A1, which mean
contract had started.
the problem lie with C1, because i want C1 color to follow B1 but i
cant use the same formula because TODAY might be in the middle of the
contract, which mean contract not ended yet, so C1 is smaller then A1,
so it wont turn red.
I want C1 to follow B1 color, no matter when is the date. and i also
want the D1 text "unpaid" color to follow the same as B1.
 
B1 start of contract 1st June 2004
C1 end of contract 30th June 2004
TODAY is 3rd June 2004

so B1 is smaller than TODAY so is red, while C1 is greater than TODAY,
so remain black.
i want C1 to follow B1 color.
 
Hi Ronnie,
Please provide a sample of several dates covering all possible
situations in a spreadsheet format showing the color you want.
It is impossible to answer your question with only one example,
which causes problems for both you and anyone who tries to answer.

show the dates like yyyy-mm-dd like
2004-06-01 so they are not ambiguous and so that they line up.

What you have said or implied so far: [blk] is the default font color

---A---- ------B-------------- -----C---------------
Task 1 [red] 2004-03-01 [red] 2004-03-31 b1 is < 2004-06-03 (today)
Task 2 [red] 2004-06-01 [red] 2004-06-02 b2 is < 2004-06-03 (today)
Task 3 [red] 2004-06-01 [red] 2004-06-30 b3 is < 2004-06-03 (today)
Task 4 [blk] 2004-07-01 [blk] 2004-07-31 b4 is future
Task 5 [red] 2000-03-01 [red] (empty) b5 < today

My guess is that you want only current tasks marked in Red
so only Task 3 would be colored red and in both B & C columns

The cells that get colored are based on the selection
and each cell in the selection gets a C.F. formula.

The formula used is based on the active cell, since we
have the columns in the formula(s) as absolute you could actually have
any cell on Row 1 as the active cell when you do your C.F. definitions.

Select columns B and C (coloring is based on selection)
Formula 1 is: =AND($b1<=TODAY, $C1>=TODAY)
-----

My real guess as to what you want:

If column C may be blank then the test would involve ISBLANK($C1)
Formula 1 is: =OR(ISBLANK($C1),AND($B1<=TODAY, $C1>=TODAY))

and the results would be
---A---- ------B-------------- -----C---------------
Task 1 [blk] 2004-03-01 [blk] 2004-03-31 b1 is < 2004-06-03 (today)
Task 2 [blk] 2004-06-01 [blk] 2004-06-02 b2 is < 2004-06-03 (today)
Task 3 [red] 2004-06-01 [red] 2004-06-30 b3 is < 2004-06-03 (today)
Task 4 [blk] 2004-07-01 [blk] 2004-07-31 b4 is future
Task 5 [red] 2000-03-01 [red*] (empty) b5 < today
Task 6 [red] 2004-08-01 [red]* (empty) future

*naturally if the coloring is to the font an empty cell will not show
any color for font.

Please set examples out as spreadsheets that is what we work with.
If is very hard to follow wording as to what goes where. You have
to spend the time to ask the question. The better you ask the
question the better and faster the answer. But it is unwise to have
200 people spending several minutes and giving up, than for you
to have spent an hour asking the question. You might find that if
you ask the question logically making it easy to be answered that
could answer it yourself (it happens a lot).

You have to be able to make it so anyone reading the question
knows the who, where, when, why, how, and how much. Try
to leave nothing to chance.

Would suggest you look over my page on Conditional Formatting:
http://www.mvps.org/dmcritchie/excel/condfmt.htm
 
maybe let have it easy here;

what formula or function do i have to key into C1 for it to follow th
font color of B1
 
It's hard to guess at what you want, even after *all* of these posts!

Here is what you said in the third post of this thread on 6/1, as a response
to Bob:
<<"A1 is today date,
B1 is contract starting date,
C1 is contract ending date.
D1 is mode of payment, (which i type "unpaid" in cell)
i set a condition, if B1 *greater* then A1 (that mean contract had
started), then i want the starting date to be in red font, this work
fine.">>

Here is what you said in the eleventh post of this thread on 6/2, as a
response to me:
<<"so B1 is *smaller* than TODAY so is red, while C1 is greater than TODAY,
so remain black">>

Do you see where you say that *YOU* set the condition for B1 to be *greater*
then A1, and then you say *smaller* then A1.

I've already asked you to tell us what formula or condition *YOU* have set
for B1, and you haven't answered this question yet.

It's important to you, because however you set B1, you must set C1 exactly
the same, for you to accomplish what you want (B1 and C1 to be the same
color).
Don't you understand that?
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------


maybe let have it easy here;

what formula or function do i have to key into C1 for it to follow the
font color of B1.
 
Back
Top