One cell will turn green when the two other cells match [Solved/Closed]

Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
- - Latest reply:  diestra123 - Feb 20, 2013 at 04:35 AM
Hello,
I am keeping a register of staff in our department. What I want to happen is for the cell (where the name is) to turn green when the date opposite the name (reporting date /date back from leave) matches the cell with formula (=today()), sort of like an alarm.
To further illustrate:
A1 = name of employee
B1 = date back from leave/reporting date
C1 = =today()

such that when B1 matches C1, A1 will turn green.
Thanks for the help!
See more 

7 replies

Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
0
Thank you
@ rizvisa1

That's 2 posts of yours that I have seen which are not helpful. Anyone can write eg: "use Conditional Format for that" or is it that you can not supply the answer!!

@ diestra 123

For your rule, select column A or a range of cells that you wish to apply the rule to > New rule > Use a formula to determine which cells to format.
Then for your rule: =AND($A1<>"",$B1=$C1) apply the formatting you require.
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
0
Thank you
thanks @rizvisa1 and Kevin@Radstock!

your inputs have both been very helpful.
the thing is, what i want to do is much more complex (at least for me) than what i had posted.
so could you please help me out again? been going in circles here. :(

i have 4 columns.
A column is for the name
B column is the start date of leave period (from)
C column is the end date of leave period (to)
D column is for the reporting date

What i want to do is for the name to be in red when he is on leave, that is, from B date to C date. after C date, the name will be green to mean that the leave period is over but he has not reported yet. the name will be clear again once i have put the reporting date in column D.

thanks for the help!
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
0
Thank you
Hi diestra123

Select column A

Rule 1 = On leave.
=AND(ISNUMBER($C1),$C1>TODAY())

Rule 2 = Leave period is over.
=AND(ISNUMBER($C1),$C1<=TODAY())

Rule 3 = Reporting date has been entered.
=AND(ISNUMBER($D1),$C1<=TODAY())

Kevin
diestra123
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
-
thanks Kevin@radstock!

problem solved!!! whew! :)
Kevin@Radstock
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9 -
Hi diestra123

Add this condition as well.

=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
0
Thank you
Hi diestra123

No problem, thank you for the feed back.

Kevin
diestra123
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
-
oooops, sorry Kevin, my problem is not completely solved.

opposite the names which already have leave schedule a month from now also turned red when i applied the format.
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
752 -
well try this
1 Select Cell A1
2, Select column A
3. Go to conditional format
4 For red
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1>TODAY(),D1="")
It says b1 date should be equal or less than today's date and c1 date should be more that today.

5. For green
=AND(A1<>"", B1<>"",C1<>"",D1="",C1<=TODAY())
Is says that c1 date is less than or equal today's date


If you ask me, you should have 3 color
1. Still on valid leave
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1>TODAY(),D1="")

2. Today leave ends
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1=TODAY(),D1="")

3, Beyond Leave period
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1<TODAY(),D1="")
this worked!!!
many thanks rizvisa1!!!
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
9
0
Thank you
Hi diestra123

Add this condition as well.

=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())

Kevin
thank you!!! everything's fine now!!
0
Thank you
thanks again!!
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
752
-2
Thank you
use Conditional Format for that