One cell will turn green when the two other cells match
Solved/Closed
diestra123
diestra123 - Feb 20, 2013 at 04:35 AM
- Posts
- 10
- Registration date
- Monday October 22, 2012
- Status
- Member
- Last seen
- July 15, 2013
diestra123 - Feb 20, 2013 at 04:35 AM
Related:
- If two cells match then return value
- If two cells match then color - Best answers
- Excel if two cells match then color - Best answers
- Compare values in two columns and return the value from third ✓ - Forum - Excel
- Need a formula to compare two columns and return a value of 1 - Forum - Excel
- If cell contains date return value ✓ - Forum - Office Software
- If cell contains text return value in another cell ✓ - Forum - Excel
- Excel if range of cell contain a text put a cell value in ✓ - Forum - Excel
7 replies
Kevin@Radstock
Feb 18, 2013 at 12:54 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
Feb 18, 2013 at 12:54 AM
@ 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.
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.
diestra123
Feb 18, 2013 at 04:38 AM
- Posts
- 10
- Registration date
- Monday October 22, 2012
- Status
- Member
- Last seen
- July 15, 2013
Feb 18, 2013 at 04:38 AM
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!
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!
Kevin@Radstock
Feb 18, 2013 at 06:04 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
Feb 18, 2013 at 06:04 AM
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
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
Feb 18, 2013 at 07:30 AM
- Posts
- 10
- Registration date
- Monday October 22, 2012
- Status
- Member
- Last seen
- July 15, 2013
Feb 18, 2013 at 07:30 AM
thanks Kevin@radstock!
problem solved!!! whew! :)
problem solved!!! whew! :)
Kevin@Radstock
Feb 19, 2013 at 07:32 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
Feb 19, 2013 at 07:32 AM
Hi diestra123
Add this condition as well.
=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Add this condition as well.
=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Kevin@Radstock
Feb 18, 2013 at 10:40 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
Feb 18, 2013 at 10:40 AM
Hi diestra123
No problem, thank you for the feed back.
Kevin
No problem, thank you for the feed back.
Kevin
diestra123
Feb 19, 2013 at 04:13 AM
- Posts
- 10
- Registration date
- Monday October 22, 2012
- Status
- Member
- Last seen
- July 15, 2013
Feb 19, 2013 at 04:13 AM
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.
opposite the names which already have leave schedule a month from now also turned red when i applied the format.
rizvisa1
Feb 19, 2013 at 07:00 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Feb 19, 2013 at 07:00 AM
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="")
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="")
Didn't find the answer you are looking for?
Ask a question
Kevin@Radstock
Feb 19, 2013 at 07:35 AM
- Posts
- 42
- Registration date
- Thursday January 31, 2013
- Status
- Member
- Last seen
- April 26, 2014
Feb 19, 2013 at 07:35 AM
Hi diestra123
Add this condition as well.
=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Kevin
Add this condition as well.
=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Kevin
rizvisa1
Feb 17, 2013 at 08:46 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Feb 17, 2013 at 08:46 AM
use Conditional Format for that
