Overriding Conditional Formatting Color with Manual Entries of a different color

[dayna]

Hi Everyone! (Sorry this is so dense)

I am trying to find a way to use conditional formatting to override my existing conditional formatting, while still retaining it underneath.
I want to do this by having the conditional format recognize when I have input a date manually VS. our pre-existing formula-made dates.
I will provide screenshots below as this is hard to explain.

The colored dates below are done with multiple conditional formatting rules, and I have provided a screenshot of those as well.
Every date is a formula, based on a date off screen that we input manually, used to create a timeline / due date for each step.
Sometimes our steps are completed before or after the date estimated by the formula, and we would then input it manually.
Ideally when we input the date manually, it would automatically change the fill color of the cell, as it is no longer a formula, and you can tell it was entered by me instead.

The greying out of the entire row happens once I enter the final completion date (that today's date has passed already) in the far right column.
I would still like this conditional format rule to apply over-top of the one I'm looking for help with creating.

I've done many google searches and have tried many different ways, but none of them are giving me exactly what I am looking for.
If this can't be done with conditional formatting please let me know how else I could get it to work. (or if this is just not a thing at all!)


Thanks!!

 

[SanjayGMusafir]

Simply add another conditional formatting with formula as below

=ISFORMULA(Cell reference of Date where you want formatting to override)

This shall test if the cell contains any formula. Simple

 

[dayna]

Simply add another conditional formatting with formula as below

=ISFORMULA(Cell reference of Date where you want formatting to override)

This shall test if the cell contains any formula. Simple

Hi Sanjay,

Thanks for helping, but that does not do what I am trying to do. I would like to select whole columns. I do not want to have to go in and select the single cell every time, I want it to be automated.
If there's a way to add multiple like this with your suggestion, and I just don't know how, feel free to let me know.

Also, the formula did not work when I selected a single cell, even though the rule was at the top of my list, and it was not a formula just a date.

I have also tried two other ways which did not work either. [ =AND(A1<>"",NOT(ISFORMULA(A1))) ] & [ =ISFORMULA(G6)=FALSE ] G6 being the first cell in my dates.
These seemed to choose which cells to format randomly, as some are dates and some are formulas, and I would not want the blanks to be filled as there are no values in them.

 

[SanjayGMusafir]

Just to explain things, I am using same in column C in my Worksheet

=ISFORMULA($C:$C)

And it's working absolutely fine. What could matter is the order of other conditions.

See example below -
Two succeeding rows have same value but one has formula and is highlighted in Blue Text

All Records.xlsb
	C
5815	Wed, 31 Aug 2022
5816	Wed, 31 Aug 2022
Bank

Cell Formulas
Range	Formula
C5815	C5815	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:L	Expression	=OR($C1="",$C1="Dt")	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),MONTH($C1)=4)	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),OR(MONTH($C1)=1,MONTH($C1)=7,MONTH($C1)=10))	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)>=(MONTH($C1048576)+1))	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),MONTH($C1)<(MONTH($C1048576)+1),YEAR($C1)>YEAR($C1048576))	text	YES
C:L	Expression	=AND(NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))),$C1048576<>"",$C1>$C1048576)	text	NO
C:C	Expression	=AND($B1="",ISFORMULA($C1),NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))))	text	NO
C:C	Cell Value	=TODAY()	text	NO
C:C	Cell Value	=TODAY()+1	text	NO
C:C	Cell Value	=TODAY()+2	text	NO
C:C	Cell Value	between TODAY()+3 and TODAY()+6	text	NO
C:C	Expression	=AND($B1048576="",ISFORMULA($C1),NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))))	text	NO

 

[dayna]

Just to explain things, I am using same in column C in my Worksheet

=ISFORMULA($C:$C)

And it's working absolutely fine. What could matter is the order of other conditions.

See example below -
Two succeeding rows have same value but one has formula and is highlighted in Blue Text

All Records.xlsb
	C
5815	Wed, 31 Aug 2022
5816	Wed, 31 Aug 2022
Bank

Cell Formulas
Range	Formula
C5815	C5815	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C:L	Expression	=OR($C1="",$C1="Dt")	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),MONTH($C1)=4)	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),OR(MONTH($C1)=1,MONTH($C1)=7,MONTH($C1)=10))	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)>=(MONTH($C1048576)+1))	text	YES
C:L	Expression	=AND($C1048576<>"",MONTH($C1)<>MONTH($C1048576),MONTH($C1)<(MONTH($C1048576)+1),YEAR($C1)>YEAR($C1048576))	text	YES
C:L	Expression	=AND(NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))),$C1048576<>"",$C1>$C1048576)	text	NO
C:C	Expression	=AND($B1="",ISFORMULA($C1),NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))))	text	NO
C:C	Cell Value	=TODAY()	text	NO
C:C	Cell Value	=TODAY()+1	text	NO
C:C	Cell Value	=TODAY()+2	text	NO
C:C	Cell Value	between TODAY()+3 and TODAY()+6	text	NO
C:C	Expression	=AND($B1048576="",ISFORMULA($C1),NOT(ISERROR(DATE(YEAR($C1),MONTH($C1),DAY($C1)))))	text	NO

Hi again Sanjay, Thank you for your quick response! I really appreciate all the time you've given to help me out.

Unfortunately, I just can't seem to make your suggestion work the way I want, and I think I might just be too dumb for this part of excel haha. I also can't find it in your rule list? Should I be typing it differently?
I've tried moving your formula you gave me up and down my list of conditional formats, turning on and off the "stop if true" while I did, but nothing at all happened to my G column.
I've uploaded a mini-sheet of my workbook so you can see exactly what's going on, without any attempt of the additional formatting I am trying to achieve.
I got all of the conditional formatting I've done from googling - I don't quite know which part of the formulas do what, I just searched and copy-pasted until I found one that actually worked for what I wanted.

Schedule - MrExcel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1				Today's Date:	31-Aug-22
2					= Enter Date / Value
3
4	blah	blah	blah	blah	blah		blah	blah	blah	blah	blah	blah	blah	blah	blah	blah	blah	blah	blah	blah	blah
5	0	Original Equation Cells	25-Aug-22	31-Aug-22	-	-	07-Sep-22	05-Oct-22	26-Oct-22	23-Nov-22	02-Nov-22	16-Nov-22	23-Nov-22	30-Nov-22	160	30-Nov-22	12-Dec-22	19-Dec-22	20-Dec-22	?	20-Dec-22
6															-
7					Rel #3						27-Jul-22	16-Jul-22		16-Aug-22	-					ASAP
8		blah			Rel #2	blah					16-Aug-22	TBD		TBD	20	03-Oct-22	10-Oct-22	N/A	N/A		10-Oct-22
9		blah			Rel #3	blah					22-Jul-22	TBD		TBD	-	19-Sep-22	26-Sep-22			ASAP	26-Sep-22
10		blah			Rel #1	blah					29-Jul-22	17-Aug-22		24-Aug-22	4	10-Oct-22	17-Oct-22			29-Aug-22	11-Oct-22
11		blah			Rel #2	blah					29-Jul-22	17-Aug-22		23-Aug-22	40	04-Oct-22	12-Oct-22			29-Aug-22	11-Oct-22
12		blah			Rel #3	blah					29-Jul-22	17-Aug-22		23-Aug-22	20	04-Oct-22	11-Oct-22			29-Aug-22	11-Oct-22
13		blah			Mock-Up	blah					13-Jul-22	20-Jul-22		20-Jul-22	-					-	31-Jul-22
14		blah			Rel #1	blah					TBD	TBD		TBD	-					01-Oct-22
15		blah				blah	26-Apr-22				-	-		-	64	03-Oct-22	07-Oct-22			10-Oct-22	10-Oct-22
16		blah			Rel #1	blah					12-Apr-22	17-May-22		17-May-22	-					27-Jun-22	25-Jun-22
17		blah			Rel #1	blah	28-Apr-22				13-Jul-22	05-Aug-22		09-Aug-22	16					-	31-Aug-22
18		blah			Rel #1	blah					30-Aug-22	TBD		TBD	-					18-Oct-22	18-Oct-22
19		blah			Rel #1	blah	03-Mar-22				27-Jun-22	27-Jun-22		03-Aug-22	-					-	08-Nov-22
20		blah			Rel #1	blah	18-Jul-22			26-Aug-22	11-May-22	28-Jul-22		29-Jul-22	110	29-Aug-22	08-Sep-22	15-Sep-22	16-Sep-22	-	26-Sep-22
21		blah			Rel #1	blah	08-Apr-22				03-May-22	18-May-22		19-May-22	20	24-Aug-22	31-Aug-22	07-Sep-22	08-Sep-22	-	09-Sep-22
22		blah				blah	08-Apr-22				18-Jul-22	03-Aug-22		03-Aug-22	50	19-Sep-22	27-Sep-22	04-Oct-22	05-Oct-22	-	26-Sep-22
23		blah				blah	14-Apr-22								142.25					-
24		blah			Rel #1	blah	25-Apr-22				16-Jun-22	12-Jul-22		20-Jul-22	80	19-Sep-22	26-Sep-22	N/A	N/A	25-Jul-22	26-Sep-22
25		blah				blah	27-May-22	05-Sep-22	26-Sep-22	24-Oct-22	03-Oct-22	17-Oct-22	24-Oct-22	31-Oct-22	30	31-Oct-22	07-Nov-22	14-Nov-22	15-Nov-22	N/A
26		blah				blah	17-Jun-22	15-Jul-22	05-Aug-22	02-Sep-22	12-Aug-22	26-Aug-22	02-Sep-22	09-Sep-22	10	09-Sep-22	16-Sep-22	23-Sep-22	24-Sep-22	15-Oct-22
27		blah				blah	20-Jul-22	17-Aug-22	07-Sep-22	05-Oct-22	14-Sep-22	28-Sep-22	05-Oct-22	12-Oct-22	1534	12-Oct-22	05-Dec-22	12-Dec-22	13-Dec-22	01-Mar-23
28		blah				blah	15-Jul-22	12-Aug-22	02-Sep-22	30-Sep-22	09-Sep-22	23-Sep-22	30-Sep-22	07-Oct-22	113	07-Oct-22	17-Oct-22	24-Oct-22	25-Oct-22	01-Nov-22
29		blah				blah									2334					01-Jun-23
30		blah				blah	16-Aug-22	13-Sep-22	04-Oct-22	01-Nov-22	11-Oct-22	25-Oct-22	01-Nov-22	08-Nov-22	95	08-Nov-22	17-Nov-22	24-Nov-22	25-Nov-22	01-Dec-23
31
schedule

Cell Formulas
Range	Formula
E1,D5	E1	=TODAY()
G5	G5	=D5+1*7
H5,J5,H30,J30,H26:H28,J25:J28	H5	=G5+4*7
I5,I30,I25:I28	I5	=H5+3*7
K5,K30,K25:K28	K5	=L5-2*7
L5:M5,L30:M30,L25:M28	L5	=M5-1*7
N5,N30,N25:N28	N5	=P5
P5,P30,P25:P28	P5	=J5+1*7
Q5,Q30,Q25:Q28,Q20:Q22,Q10:Q12,Q8	Q5	=P5+(O5/32)+7
R5,R30,R25:R28,R20:R22	R5	=Q5+1*7
S5,S30,S25:S28,S20:S22	S5	=R5+1
U5	U5	=S5
Q15	Q15	=P15+(O15/32)+2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:C,E:N,P:S,U:U	Expression	=AND($U1<>"",$U1<TODAY())	text	NO
G:N,P:S	Cell Value	between TODAY()+70 and TODAY()+140	text	NO
G:N,P:S	Cell Value	between TODAY()+35 and TODAY()+70	text	NO
G:N,P:S	Cell Value	between $E$1+2 and TODAY()+35	text	NO
G:N,P:S	Cell Value	between TODAY()+2 and $E$1	text	NO
G:N,P:S	Cell Value	between TODAY()-5000 and TODAY()+2	text	NO

 

[SanjayGMusafir]

Check below, including Conditional Formatting Order.

Hope it helps

All Records.xlsb
	B	C
2	9/1/22	FALSE
3	9/1/22	FALSE
4	9/1/22	FALSE
5	9/1/22	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=ISFORMULA(B2)
B5	B5	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C5	Expression	=ISFORMULA(B2)	text	NO
B2:B5	Dates Occurring	today	text	NO

 

[dayna]

Check below, including Conditional Formatting Order.

Hope it helps

All Records.xlsb
	B	C
2	9/1/22	FALSE
3	9/1/22	FALSE
4	9/1/22	FALSE
5	9/1/22	TRUE
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=ISFORMULA(B2)
B5	B5	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:C5	Expression	=ISFORMULA(B2)	text	NO
B2:B5	Dates Occurring	today	text	NO

Hi Sanjay, Thank you!!

I think I was looking for a solution without having to create extra columns, but this works exactly how I wanted, so extra columns it is! Will simply hide them away haha.

Thanks again!
Dayna

 

[SanjayGMusafir]

Hi Sanjay, Thank you!!

I think I was looking for a solution without having to create extra columns, but this works exactly how I wanted, so extra columns it is! Will simply hide them away haha.

Thanks again!
Dayna

I showed you extra column to tell you where exactly the formula was - You don't need to create extra column

Just keep Formula testing condition above the other conditions on the same column. Simple

 

[dayna]

I showed you extra column to tell you where exactly the formula was - You don't need to create extra column

Just keep Formula testing condition above the other conditions on the same column. Simple

Well in that case, your previous reply I marked as my solution would still not work for me in a single column, as your condition "=isformula(b2)" highlights the formula cells, and what I'm trying to do is the opposite and only highlight cells that have manually inputted text/dates in them. I have also tried "=istext(g6)" but that does not work either as my dates are dates and not text, whether or not I input them manually or use formulas.

I suppose I will simply keep the extra columns - can't figure out any way to make it work all in one.

 

[MARK858]

Not home to test but can't you use

=NOT(ISFORMULA(B2))