How to stop "Text" function overriding conditional formating

[JpMakako]

Hi, I recently developed a lambda that basically uses the TEXT function to format values (date, currency, etc) depending on the data Im pulling to a specific cell: Example: I have a list of apartment units and i want to know the date they were sold, the amount (value), phone number pulling it from a data base with a simple XlookUp. I used the text function to show the proper formatting. Then I tried to insert a conditional formatting to show differences in values with color aid. The thing is the colored background is overwritten when using TEXT. Is there a way around it?

 

[Sufiyan97]

You need to post some sample of your data and the condition you are using in conditional formatting

 

[JpMakako]

Libro1
	A	B	C	D	E	F	G	H	I
1
2	Data						Both Conditional Format
3		Date	Currency	Text			No Format	Format
4	Unit	Date	Value	Phone		Unit	Date	Date
5	101	14/04/2018	$ 756.856,00	XXX-XXXXXX1		101	43204	18-04-14
6	102	16/12/2017	$ 575.352,00	XXX-XXXXXX2		102	43085	17-12-16
7	201	5/11/2017	$ 532.673,00	XXX-XXXXXX3		201	43044	17-11-05
8	202	20/05/2018	$ 648.128,00	XXX-XXXXXX4		202	43240	18-05-20
9	301	14/04/2018	$ 795.728,00	XXX-XXXXXX5		301	43204	18-04-14
10	302	3/11/2017	$ 793.177,00	XXX-XXXXXX6		302	43042	17-11-03
11
Hoja1

Cell Formulas
Range	Formula
H4	H4	=LOOKUP(G4,B4:D4,B3:D3)
G5:G10	G5	=XLOOKUP(F5,$A$5:$A$10,OFFSET($A$5:$A$10,0,XMATCH($G$4,$B$4:$D$4)))
H5:H10	H5	=IFS($H$4="Currency",TEXT(G5,"$ #,#0.0"),$H$4="Date",TEXT(G5,"yy-mm-dd"),$H$4="Text","@")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H5:H10	Other Type	Color scale		NO
G5:G10	Other Type	Color scale		NO

Cells with Data Validation
Cell	Allow	Criteria
G4	List	=$B$4:$D$4