1

The following line is not working:

If Worksheets(Specialist).Cells(projectrow, WeekLoop + 4).Interior.Color = ReferenceCellColorPlanned.Interior.Color Then

where "ReferenceCellColorPlanned" is an user-input range in the formula

where "Specialist" is a string (and the worksheet does exist)

where "projectrow" and "WeekLoop" are integers

The problem is it always goes through the "If" criteria, no matter what the actual background is. So I tried to debug and set the following (simplified code, only taking out the bits that are needed)

Dim Cel1 as Range
Set Cel1 = Worksheets(Specialist).Cells(projectrow, WeekLoop + 4)
If Cel1.Interior.Color = ....

Then what I noticed is Cel1 actually returns a string value of what is the value of the cell, instead of the range value (which I find weird since as far as I understand, "Cells" is by default a range object and I declared Cel1 as a Range Variable).

Kindly help me understand why worksheets.cells is returning a string instead of a range, and how to make it return the range so I can check its background color. Thanks!

EDIT: I've always tried worksheets.Range(Cells()) as well, and it doesn't work either

DWReyes
  • 83
  • 12

2 Answers2

1

Excel and VBA are in general user-friendly. Thus, the object Cell returns something, that would make sense to the user – its value, and not something a bit useless like the address of the object, which would someone working with Java expect (see: What's the simplest way to print a Java array?):

int[] intArray = new int[] {1, 2, 3, 4, 5};
System.out.println(intArray);     // prints something like '[I@3343c8b3'

If you are familiar with Python, consider that the object Cells have a __repr__ method implemented, which returns their value - What is the difference between __str__ and __repr__?

In VBA, the __repr__ is achieved with the Default Member attribute:

Concerning the .Interior.Color property, you may access it easily like this and see the values you are comparing:

Public Sub TestMe()

    Debug.Print Worksheets(1).Cells(1, 1).Interior.Color
    Debug.Print Worksheets(2).Range("A10").Interior.Color

End Sub
double-beep
  • 5,031
  • 17
  • 33
  • 41
Vityata
  • 42,633
  • 8
  • 55
  • 100
  • 1
    While your comparison is accurate, I think that it's better to refer to a native [`default member`](http://www.cpearson.com/excel/DefaultMember.aspx) of the class, than to `__repr__`. Actually, default member of the `Range` is well covered [here](https://stackoverflow.com/questions/32996772/is-value-actually-the-default-property-of-the-range-object). – CommonSense May 24 '18 at 09:48
  • @CommonSense - added to the answer. – Vityata May 24 '18 at 10:02
0

Even though the Cell is returning its Value when getting printed via MsgBox, you should still be able to compare the Cell Colors since the variable itself remains an Range Object.

Following sample code works fine for me.

Sub test()
  Dim Cel2 As Range
  Dim Cel3 As Range
  Set Cel2 = Worksheets(1).Cells(1, 1)
  Set Cel3 = Worksheets(1).Cells(2, 2)
  If Cel2.Interior.Color = Cel3.Interior.Color Then
    MsgBox ("YES")
  Else
    MsgBox ("NO")
  End If
End Sub
Philipp Jansen
  • 3
  • 3