One cell will turn green when the two other cells match [Solved/Closed]
diestra123
- Posts
- 10
- Registration date
- Monday October 22, 2012
- Last seen
- July 15, 2013
Related:
- One cell will turn green when the two other cells match
- If two cells match, return value from third ✓ - Forum - Excel
- Comparing columns and returning value ✓ - Forum - Excel
- Excel partial cell match - How-To - Excel
- Excel - IF is error with 2 cells match - How-To - Excel
- Excel function to Check neighbour cells match for all ✓ - Forum - Excel
7 replies
@ 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.
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!
- Posts
- 4475
- Registration date
- Thursday January 28, 2010
- Status
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="")