CODEWITHSUNDEEP
excelvbafunction
dwc309
dwc309

Reputation: 23

vba function to return a cell background color

As the subject says, I need a function to return the cell background color. My vba function is

Public Function ReturnedBackGroundColor(rnge As Range) As Integer
    ReturnedBackGroundColor = rnge.Offset(0, 0).Interior.Color
End Function

In the cell, I have

=IF(ReturnedBackGroundColor("G9") =3,1,2)

and I get a #VALUE! error. I have searched for a couple of hours off and on this morning and get no joy.

I have tried putting the function in both the sheet code and a module. I have tried Color and ColorIndex. I have tried 3 and vbRed (which BTW isn't recognized by VBA for reasons that I have forgotten. I have tried making the function public in both the sheet code and a module. I have tried recalculating the sheet in both cases.

What I suspect is happening is that I have to set something up in order to make the connection between the two for both the vbred and the function to the sheet.

This is the main issue with these forums. People put up working code but they don't tell you the shtuff that has to go around the code in order for it to work. And that is most likely what I am missing here.

Upvotes: 1

Views: 2527

Answers (2)

Gary's Student
Gary's Student

Reputation: 96753

Minor changes:

Public Function ReturnedBackGroundColor(rngeADDY As String) As Long
    ReturnedBackGroundColor = Range(rngeADDY).Interior.Color
End Function

enter image description here

we:

  • pass a string
  • return a long

You can choose to pass either a String or a Range. But there should be consistency between the usage in the worksheet cell and the VBA coding.

Note:

The usual choice is to pass a Range as this helps to establish the correct level of volatility.

Upvotes: 1

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Remove .Offset(0, 0), return a Long (your code is fine otherwise), and give it a Range instead of a String:

=IF(ReturnedBackGroundColor(G9)=3,1,2)

G9 being the actual cell reference, rather than a string literal containing a cell address.

That will be more reliable than building the Range from the string inside the function, since when you're invoking it from VBA code you will not want to assume what sheet is currently active.

Upvotes: 1

Related Questions