Forum Discussion

domregas's avatar
domregas
Copper Contributor
Jan 19, 2024

formula using format of a cell for if() condition

I would like to use a formula in cell E1 that checks if A1 is filled yellow (or highlighted yellow (255,255,0)). if it is yellow, I want E1 to = B1, if not leave blank.
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Jan 20, 2024

domregas If you have Excel for MS365, you could try creating a custom LAMBDA function in Name Manager using the Excel 4.0 macro function, GET.CELL. On the ribbon, go to Formulas > Define Name, call it FILLCOLOR and use the following formula:

 

=LAMBDA(reference,GET.CELL(63,reference))

 

Or, to make it volatile, use the NOW function as follows:

 

=LAMBDA(reference,GET.CELL(63/NOW()^0,reference))

 

Define Name: FILLCOLOR Function

 

Once created, you can then use the custom FILLCOLOR formula in cell E1 to return B1 if the background color of cell A1 is 6 (yellow):

 

=IF(FILLCOLOR(A1)=6, B1, "")

 

FILLCOLOR Test Results

 

Note: the results will not update automatically when the background color is changed. If the volatile method has been used (see above), the formulas can be refreshed by pressing F9 (Calculate Now) or Shift+F9 (Calculate Sheet) on your keyboard. Also, the workbook must be saved as a Macro-Enabled Workbook (.xlsm), and the option to "Enable Excel 4.0 macros when VBA macros are enabled" must be set in File > Options > Trust Center > Trust Center Settings > Macro Settings.

Resources