Stop conditional formatting in one cell when adjacent cell contains data

[win1forgipp]

This sheet monitors individual's physical fitness scores. If members score <90 they must retest every 6 months with a diagnostic performed two months prior (4 months mark). If they score 90 or higher they will retest yearly with a diagnostic performed at 10 months.

I have several rows of dates, corresponding to when an individual took the test. In the second column is the score the individual received. The third column contains this formula:

=IF(B4>89.9,(A4+300),(A4+120))

This formula is adding 300 days to the next time the individual tests vice 120 if they scored under a 90%. (I'm sure there is a better way than 300 because thats not accurate)

A4 represents the date they took the test.

The third column is conditionally formatted with three rules. IF date is THIS month, it turns green, LAST month it turns red and NEXT month it turns yellow.

My question resides with my 4th column...In this column will be the date they performed the diagnostic to judge progress before actual test.

When I place a date in that field, I'd like the column that is conditionally formatting based on dates to stop formatting color and remain white basically showing on a spreadsheet that they were once overdue (red) but have since taken the diagnostic which puts them back in the white.

Here is the file on my Drive account.
https://drive.google.com/file/d/0By3TusOiWiVUb0V6cE5kbVF1T0k/view?usp=sharing

Thanks for helping me out.

cheers,
Gipp

 

[oldbrewer]

and( D1="", rest of rule)

 

[win1forgipp]

+oldbrewer, thanks for the reply but I'm not sure what you mean here. Is this formula to be appended onto an existing cell's formula or added into cond formatting? I tried adding it to the cell's existing formula and excel did not like that. =IF(G2>89.9,E2+300,(E2+120))AND(K2="", ISTEXT)

What am I missing here? Thanks!!!!

gipp

 

[Peter_SSs]

What am I missing here?

You need to remove the existing Conditional Formatting from column C (created from from 'Highlight Cells Rules') and apply the following.
Note that when entering the CF dialog, you need to choose

New Rule... -> Use a formula to determine which cells to format -> then use the formulas shown below my screen shot.

Excel Workbook
	C	D
3	Test Due	Diagnostic
4	26-Sep-16
5	31-Mar-16
6	1-Apr-16
7	22-Dec-15	04-Dec-15
8	18-Feb-16	02-Feb-16
9	19-Jan-16
10	5-Apr-16
11	6-Apr-16
12	7-Apr-16
13	22-Dec-15
14	18-Feb-16
Sheet1 (2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4	1. / Formula is =AND(D4="",EOMONTH(TODAY(),1)=EOMONTH(C4,0))	Abc
C4	2. / Formula is =AND(D4="",EOMONTH(TODAY(),-1)=EOMONTH(C4,0))	Abc
C4	3. / Formula is =AND(D4="",EOMONTH(TODAY(),0)=EOMONTH(C4,0))	Abc

 

[oldbrewer]

Say you want to colour cell G1 red if A1=3

in G1 you would put =A1=3

now say you want to cancel that colouring if B1=0

the conditional formatting code becomes

=and(A1=3,B1<>0)

 

[Peter_SSs]

Say you want to colour cell G1 red if A1=3

in G1 you would put =A1=3

now say you want to cancel that colouring if B1=0

the conditional formatting code becomes

=and(A1=3,B1<>0)

I think it didn't make sense to the OP because they didn't have a formula rule in the CF to start with, so there was no formula to add that condition to.

 

[oldbrewer]

thanks, Peter.

 

[oldbrewer]

The third column is conditionally formatted with three rules. IF date is THIS month, it turns green, LAST month it turns red and NEXT month it turns yellow.

although I am now confused...

 

[win1forgipp]

+Peter, +oldbrewer, thank you both for replying....however, I cannot get this to function correctly. When I apply the condition, random cells in the column are reacting to other cells changing dates. I know that sounds bizarre but perhaps if I showed you on a Google Sheet where we could all edit and comment that would make more sense? Just trying to think of a way to collaborate.

I know Sheets is a little different than Excel, but formatting remains the same. Just a thought. Thank you both for the help.




gipp

 

[win1forgipp]

Forgot the link if you want to download it and give it a try. Probably easier for you to work with if you have the product. Besides, usually a second or third set of eyes sees a better way to do it.

EDIT: Wrong file...sorry:

https://drive.google.com/file/d/0By3TusOiWiVUcWZObEcxNTMtcjQ/view?usp=sharing


gipp