One cell will turn green when the two other cells match

Solved/Closed

diestra123 Posts 9 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
Conditional formatting if cell contains text - Excel Forum
Excel conditional formatting if another cell contains specific text ✓ - Excel Forum
Music match jukebox - Download - Audio playback
If two cells match return value from third excel ✓ - Excel Forum
Clear only the formatting from the selected cell (leaving the content) - Guide

7 responses

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 9 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 9 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 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
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!