Highlight cells with same formula

[jewelichiii]

Hi. I am having difficulties in finding ways to highlight cells with the same formula.
For example:

The cell highlighted in yellow has the =X + no. of days format. Whereas the image below has the =EDATE(x,x) formula.

I need the cells with same formula to be highlighted in one color, same with the other formula with another color.

This file is used for batch number assignments in our food manufacturing plant and analysts frequently overlook and make errors.
Hoping someone can solve my dilemma. Thank you!

 

[etaf]

are you trying to conditional format based on the formula in the cell

you could use MID(FORMULATEXT(B2),2,5)="EDATE"

Not sure about the other formula , unless we base it on the + exists in the formula
=ISNUMBER((FIND("+",FORMULATEXT(B2),1)))

something like

Book7
	A	B	C	D	E
1
2		182	82	EDATE
3
4		165		=A1+165	TRUE
5					FALSE
6		91			FALSE
7
8		3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=EDATE(A2,6)
C2	C2	=MID(B2,2,4)
D2	D2	=MID(FORMULATEXT(B2),2,5)
B4	B4	=A1+165
D4	D4	=FORMULATEXT(B4)
B6	B6	=EDATE(A6,3)
E4:E6	E4	=ISNUMBER((FIND("+",FORMULATEXT(B4),1)))
B8	B8	=1+2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B10	Expression	=ISNUMBER((FIND("+",FORMULATEXT(B1),1)))	text	NO
B1:B10	Expression	=MID(FORMULATEXT(B1),2,5)="EDATE"	text	NO

Note: Images are difficult to see , and also requires that I input all the data myself, which is very time consuming.

A SMALL sample spreadsheet, around 10-20 rows, would help a lot here, with all sensitive data removed, and expected results mocked up and manually entered, with a few notes of explanation.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC , then put the sample spreadsheet onto a share
I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.

 

[jewelichiii]

Hi. I am really sorry and I am grateful for your response. I cannot download the add-ins since this is a company-owned computer.
Here is the link of the file: MrExcel

 

[etaf]

thats a word document ?

did you try my example - did it work, if not , why?

if its always +365
then we could change the + formula for +365

which i have updated below

does this work ?

Book7
	A	B
1
2		182
3
4		365
5
6		91
7
8		3
Sheet1

Cell Formulas
Range	Formula
B2	B2	=EDATE(A2,6)
B4	B4	=A1+365
B6	B6	=EDATE(A6,3)
B8	B8	=1+2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B10	Expression	=ISNUMBER((FIND("+365",FORMULATEXT(B1),1)))	text	NO
B1:B10	Expression	=MID(FORMULATEXT(B1),2,5)="EDATE"	text	NO

 

[jewelichiii]

Tried it now! It worked!

Thank you very much!

 

[etaf]

you are welcome