One cell will turn green when the two other cells match

Solved/Closed

diestra123 Posts 10 Registration date Monday October 22, 2012 Status Member Last seen July 15, 2013 - Feb 17, 2013 at 03:06 AM
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!

Conditional formatting if two cells match
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
Conditional formatting if another cell contains any text ✓ - Excel Forum
Conditional format based on another cell containing text - Excel Forum
Excel conditional formatting if cell contains multiple specific text ✓ - Excel Forum
If cell contains text then return value multiple conditions ✓ - Excel Forum

7 replies

Answer 1 / 7

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
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.

Answer 3 / 7

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
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

diestra123 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! :)

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Feb 19, 2013 at 07:32 AM

Hi diestra123

Add this condition as well.

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

Answer 4 / 7

Kevin@Radstock Posts 42 Registration date Thursday January 31, 2013 Status Member Last seen April 26, 2014 9
Feb 18, 2013 at 10:40 AM

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
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.

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
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="")

diestra123
Feb 20, 2013 at 04:34 AM

this worked!!!
many thanks rizvisa1!!!

Similar discussions

Conditional formatting: change fontcolor based on another cell

How to use conditional format based on text?

Condition formatting on changing cell color based on another cel

if sum>= 60, color cells red

Formula for conditional format to color cells

Change colour of range of cells based upon text from a drop down menu in another

Similar discussions

Subscribe To Our Newsletter!