Excel Video 439 puts several of the things we’ve learned over the past Excel Videos together to build a custom function in VBA. The custom function is a response to a question a while back in MGMA’s Excel Users group asking for a way to compare the fill color of cells.
Notice a few things that are different about custom functions. Instead of starting the code like a subroutine with Sub and End Sub, we change to Function and End Function. Our function takes two parameters, the two cells we’re comparing to see if the fill color matches. The result of the function is a string, or text. The heart of the function is an if statement that compares the fill colors of the two cells. If the fill colors match, the function returns the word “Match.” If the fill colors don’t match, the function leaves the cell blank.
The big caveat to this function is that Excel doesn’t monitor changes in a cell’s fill color like it does in a cell’s value. If the fill color of a cell changes, the formula doesn’t catch the change unless you edit the formula. The important part of this video is how a custom function works. We’ll create a more useful function that’s more applicable to the average practice in the next video. I look forward to seeing you then.
