CODEWITHSUNDEEP
excelexcel-formulaexcel-2010vba
Carol.Kar
Carol.Kar

Reputation: 5345

Compare two cells based on their color

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!

Upvotes: 1

Views: 12527

Answers (1)

BrakNicku
BrakNicku

Reputation: 5993

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.

Upvotes: 1

Related Questions