CFLOGIC

[schardt679]

CFLOGIC is used for Conditional Formatting and returns TRUE or FALSE given a function number (1-19) related to various logic and a cell reference.

Excludes blanks (aside from Func 1,2) but not 0's. Functions are 1☛Blank, 2☛Not Blank, 3☛Number, 4☛Not Number, 5☛Text, 6☛Not Text, 7☛Logical, 8☛Not Logical, 9☛Error, 10☛Not Error, 11☛Formula, 12☛Not Formula, 13☛Odd, 14☛Even, 15☛Value Found (not case sensitive), 16☛Value Not Found (not case sensitive), 17☛Value Found (case sensitive), 18☛Value Not Found (case sensitive), 19☛Value Changes
Functions 15-18 use partial match functionality

=LAMBDA(Func_Num,Cell_Ref1,Cell_Ref2,Value,
      LET(Func, Func_Num,    Ref_1, Cell_Ref1,
             Ref_2, Cell_Ref2,    Val, Value,
             BL, Ref_1="",    BL_N, NOT(BL),
             NUM, ISNUMBER(Ref_1),    NUM_N, NOT(NUM),
             TXT, ISTEXT(Ref_1),    TXT_N, NOT(TXT),
             LOG, ISLOGICAL(Ref_1),    LOG_N, NOT(LOG),
             ERR, ISERROR(Ref_1),    ERR_N, NOT(ERR),
             CALC, ISFORMULA(Ref_1),    CALC_N, NOT(CALC),
             ODD, ISODD(Ref_1),    EVEN, ISEVEN(Ref_1),
             FND_NCS, ISNUMBER(SEARCH(Val, Ref_1)),    FND_NCS_N, NOT(FND_NCS),
             FND_CS, ISNUMBER(FIND(Val, Ref_1)),    FND_CS_N, NOT(FND_CS),
             VAL_CHG, AND(Ref_1<>Ref_2, Ref_2<>""),
             F1_10, SWITCH(Func,  1, BL,  2, BL_N,  3, NUM,  4, NUM_N,  5, TXT,  6, TXT_N,  7, LOG,  8, LOG_N,  9, ERR,  10, ERR_N,  0),
             F11_19, SWITCH(Func,  11, CALC,  12, CALC_N,  13, ODD,  14, EVEN,  15, FND_NCS,  16, FND_NCS_N,  17, FND_CS,  18, FND_CS_N,  19, CHG,  0),
             Result, IF(--(F1_10)=1, F1_10, F11_19),
             Return, IF(AND(Func>2, IFERROR(BL, 0)), 0, Result),
             Return
      )
  )

LAMBDA Examples.xlsx
	A	B	C	D	E	F	G	H	I	J
1		CFLOGIC
2
3		Sample Data					Conditional Formatting Colors
4		Is Blank	Not Blank				Func_Num	Function
5		99	99				1	Is Blank (BL)
6							2	Not Blank (BL_N)
7
8		86	86
9		93	93
10
11		74	74
12
13
14		Sample Data					Conditional Formatting Colors
15		Is Number	Not Number	Is Text	Not Text		Func_Num	Function
16		91	91	Patrick	Patrick		3	Is Number (NUM)
17		59	59	87	87		4	Not Number (NUM_N)
18		72	72	75	75		5	Is Text (TXT)
19		#N/A	#N/A	#N/A	#N/A		6	Not Text (TXT_N)
20		ABC	ABC	ABC	ABC
21		60	60	60	60
22		82	82	82	82
23
24
25		Sample Data					Conditional Formatting Colors
26		Is Logical	Not Logical	Is Error	Not Error		Func_Num	Function
27		91	91	EFG	EFG		7	Is Logical (LOG)
28		TRUE	TRUE	#VALUE!	#VALUE!		8	Not Logical (LOG_N)
29		ABC	ABC	No	No		9	Is Error (ERR)
30		#N/A	#N/A	#N/A	#N/A		10	Not Error (ERR_N)
31		FALSE	FALSE	#CALC!	#CALC!
32		#VALUE!	#VALUE!	HIJ	HIJ
33		TRUE	TRUE	123	123
34
35
36		Sample Data					Conditional Formatting Colors
37		Is Formula	Not Formula	Is Odd	Is Even		Func_Num	Function
38		91	91	74	74		11	Is Formula (CALC)
39		TRUE	TRUE	96	96		12	Not Formula (CALC_N)
40		FALSE	FALSE	71	71		13	Is Odd (ODD)
41		#N/A	#N/A	84	84		14	IS Even (EVEN)
42		TRUE	TRUE	93	93
43		60	60	99	99
44		82	82	100	100
45
46
47		Sample Data					Conditional Formatting Colors
48		Found (Not CS)	Not Found (Not CS)	Found (CS)	Not Found (CS)		Func_Num	Function	Value
49		Pittsburgh	Pittsburgh	Pittsburgh	Pittsburgh		15	Found (Not CS) (FND_NCS)	C
50		Oakdale	Oakdale	Oakdale	Oakdale		16	Not Found (Not CS) (FND_NCS_N)	C
51		Newell	Newell	Newell	Newell		17	Found (CS) (FND_CS)	C
52		Virginia Beach	Virginia Beach	Virginia Beach	Virginia Beach		18	Not Found (CS) (FND_CS_N)	C
53		Deep Creek	Deep Creek	Deep Creek	Deep Creek
54		Clarington	Clarington	Clarington	Clarington
55		Bonita Springs	Bonita Springs	Bonita Springs	Bonita Springs
56
57		*CS = Case Sensitive
58
59
60		Sample Data					Conditional Formatting Colors
61		Date	Product	Units Sold			Func_Num	Function
62		06/28/2021	Product 1	90			19	Value Changes (VAL_CNG)
63		06/28/2021	Product 2	80
64		06/28/2021	Product 3	74
65		06/29/2021	Product 1	92
66		06/29/2021	Product 2	85
67		06/30/2021	Product 1	89
68		06/30/2021	Product 2	93
69
CFLOGIC

Cell Formulas
Range	Formula
B38	B38	=91
B40	B40	=2>3
B43	B43	=30*2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B5:B11	Expression	=CFLOGIC(1, B5,,)	text	NO
C5:C11	Expression	=CFLOGIC(2, C5,,)	text	NO
B16:B22	Expression	=CFLOGIC(3, B16,,)	text	NO
C16:C22	Expression	=CFLOGIC(4, C16,,)	text	NO
D16:D22	Expression	=CFLOGIC(5, D16,,)	text	NO
E16:E22	Expression	=CFLOGIC(6, E16,,)	text	NO
B27:B33	Expression	=CFLOGIC(7, B27,,)	text	NO
C27:C33	Expression	=CFLOGIC(8, C27,,)	text	NO
D27:D33	Expression	=CFLOGIC(9, D27,,)	text	NO
E27:E33	Expression	=CFLOGIC(10, E27,,)	text	NO
B38:B44	Expression	=CFLOGIC(11, B38,,)	text	NO
C38:C44	Expression	=CFLOGIC(12, C38,,)	text	NO
D38:D44	Expression	=CFLOGIC(13, D38,,)	text	NO
E38:E44	Expression	=CFLOGIC(14, E38,,)	text	NO
B49:B56	Expression	=CFLOGIC(15, B49,, $I$49)	text	NO
C49:C56	Expression	=CFLOGIC(16, C49,, $I$50)	text	NO
D49:D56	Expression	=CFLOGIC(17, D49,, $I$51)	text	NO
E49:E56	Expression	=CFLOGIC(18, E49,, $I$52)	text	NO
B62:D68	Expression	=CFLOGIC(19, $B62, $B63,)	text	NO

 

[schardt679]

When XL2BB copies the range it does not grab the border color and the border style. The section for function 19 - value changes should look like the attached picture. I had to use bold to show the change instead. It detects that there is a border but uses the default black solid line.