Formatting Cell Based on content using Conditional Formatting

[itsrich]

I want the cell containing the following formula to change the fill color when the formula results in a date. =IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D3,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D3,'Where is production'!$D:$D,0),5))

The result of the formula is text or number

Currently, I am attempting to use conditional formatting to format the cell, but the text and numbers are formatted the same. I can figure out how to determine the difference between a number and text.

TY for your help

 

[etaf]

i thought futher from my previous post to show an example of things out of sync

Book2
	B
1
2	44958
3	x
4	44960
5	x
6	44962
7	x
8	44964
9	x
10	44966
11	x
12	44968
13	x
14	44970
15	x
16	44972
Sheet2

Cell Formulas
Range	Formula
B2,B16,B14,B12,B8:B10,B6,B4	B2	=A2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B1:B16	Expression	=ISNUMBER(B2)	text	YES

here i have selected $B$1:$B$16
but the formula is =Isnumber(B2)

so the conditional format - looks at B1 as thats the start of the selection -
then it uses the formula and checks if its a number- BUT the formula says look at B2
B2 is a number , so thats TRUE , and hightlight the cell - but as its looking at B1 it highlights B1 - wrong cell
then
it moves down the Selection, and looks at B2 to highlight - and formula now checks B3, so tests B3 is false - so does not highlight B2
then
it moves down the Selection, and looks at B3 to highlight - and formula now checks B4, so tests B4 is TRUE - so does not highlight B3

hence all the conditional formatting is 1 row out

 

[itsrich]

OK, solved it. Simplified conditional formatting. @etaf gave a good explanation of how conditional formatting works. TYVM

I left the =ISTEXT(A2) conditional formatting off because some cells that appear blank, changed color as if they were text. I also changed my mind about the need for these to be a contrasting color.

End result, @etaf helped me solve the problem. I modified the formula to =AND(ISNUMBER(M3),M3<>"",M3>0), using the knowledge shared by @esaf. Successful outcome!

2023.02.14 FAB Tracking & Reporting Sheet.xlsx
	M
23	18-Jan
24	18-Jan
25	5-Dec
26	16-Dec
27	X
28	18-Jan
29	16-Dec
30	16-Dec
31	18-Jan
32	18-Jan
33	30-Jan
34	30-Jan
35	24-Jan
36	19-Jan
37	18-Jan
38	19-Jan
39	19-Jan
40	WIP
41	WIP
42	18-Jan
43	18-Jan
44	WIP
45	WIP
46	25-Jan
47	25-Jan
The Time Line CURRENT

Cell Formulas
Range	Formula
M23:M47	M23	=IF(ISBLANK(INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5)),"",INDEX('Where is production'!$G:$N,MATCH('The Time Line CURRENT'!D23,'Where is production'!$D:$D,0),5))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M3:M304	Expression	=AND(ISNUMBER(M3),M3<>"",M3>0)	text	NO

 

[etaf]

i'm glad you have it sorted out now and getting the results you want
very welcome