What you want to do is just not possible with the native functions. Creating a UDF might be the only solution.The formula you created with native functions are just too long with too many repeated inputs so it’s hard to read. Creating a UDF can simplify this.
If you’re not very familiar with VBA, how to open the visual basic editor and where to put the code, then you may want to read this post about How To Use The VBA Code You Find Online. In our example we are going to create a UDF that returns the cell colour from the referenced cell. It will be a very simple function with only three lines of code, but there is no native Excel function that can do this and a UDF is the only solution.
In the example, we have a set of customer order data but the status has been colour coded into the Order ID cells. Having colour coded data can look nice, but it can be hard to analyse the data with formulas and pivot tables. To translate this colour coding into tangible data, we will create a UDF that takes a cell as input and returns the cell format colour.
Open the visual basic editor (VBE) by either using the keyboard shortcut Alt + F11 or going to the Developer tab and pressing the Visual Basic button under the Code section. All UDF’s will have these main components to the code.
Opening and closing – Must open with Function and close the code with End Function.Function name – Must use a unique name that’s not already used by the system.Variable inputs – This is where you can declare the arguments of your UDF. You can have empty brackets () if your function has no arguments.Code – Code for your UDF should assign a value to your function name.
Now we can use our UDF in our spreadsheet in the usual manner that we use other native Excel functions. Since we saved the VBA locally in a module in the file, we can only use the UDF in this particular spreadsheet and it will not be available globally.
