Compare two cells based on their color

Question

I want to compare two cells based upon their color they are having.

I created the following function. If the two colors in a cell match then it should write Same and color the cell green in the cell, else 'Change' and color it red.

However, I am currently getting:

enter image description here

In the #Value cells my formula is =ColorComparer(H4;C4)

Below you can find my vba function that I created:

Function ColorComparer(rColor As Range, rRange As Range)

Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex

If rCell.Interior.ColorIndex = lCol Then
    vResult = "Same"
    vResult.Interior.ColorIndex = RGB(0,255,0)
Else
    vResult = "Change"
    vResult.Interior.ColorIndex = RGB(255,0,0)
End If

ColorComparer = vResult
End Function

I really apprecaite your replies!

Best regards!

i do not understand your code at all. What are you trying to do? You have two tables and want to compare the colors from the tables or am I reading this wrong? — Bogdan Banciu
@BogdanBanciu Thx for your answer Bogdan. My goal with my color function is to compare the color of the cells of two tables in the picture. As input, I give my function one cell of each table and compare it by color. As an output I would like to have inside my third table Changed with background color red or Same with background color green — Carol.Kar

BrakNicku BrakNicku · Accepted Answer · 2015-05-05T10:02:11

With some small changes, your UDF gives correct (Same/Change) results:

Function ColorComparer(rColor As Range, rRange As Range) As String

Dim rCell As Range
Dim lCol As Long
Dim vResult as String 

lCol = rColor.Interior.Color

If rRange.Interior.Color = lCol Then
    vResult = "Same"
'   vResult.Interior.ColorIndex = RGB(0, 255, 0)
Else
    vResult = "Change"
'   vResult.Interior.ColorIndex = RGB(255, 0, 0)
End If

ColorComparer = vResult
End Function

List of changes:

replaced rCell with rRange parameter. rCell does not have any value assigned
replaced ColorIndex with full RGB value Color
commented both vResult.Interior lines. vResult does not point to current cell, and even if it did, there is a number of limitations of UDFs:

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

Insert, delete, or format cells on the spreadsheet.

Change another cell's value.

Move, rename, delete, or add sheets to a workbook.

Change any of the environment options, such as calculation mode or screen views.

Add names to a workbook.

Set properties or execute most methods.

You can use conditional formatting of the result range to overcome those limitations.

The trickiest part about this UDF is it's recalculation. Even if you define it as volatile with an additional line:

Application.Volatile True

changing cell's background will not trigger recalculation.

The only solution I know is to trigger sheet recalculation in sheet's SelectionChanged and Activate event handlers.

Compare two cells based on their color

1 Answers