0

I have a function like so:

Function cellcolor(r As Range) As Long
  cellcolor = r.Interior.Color
End Function

I then reference it in Cell B1 like so:

=cellcolor(A1)

The color of A1 is red, and therefore the output is 255.

I want to set the color of B1 to match that of A1 but I'm unsure how. Is it possible?

I've tried:

Function cellcolor(r As Range) As Long
  cellcolor.Interior.Color = r.Interior.Color
End Function

But it doesn't work, any ideas?

Thanks

EDIT:

As pointed out in the comments, a function cannot make cell format changes. I therefore used the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)

  For Each ws In ActiveWorkbook.Worksheets
    For Each cell In ws.Range("B1")
      If Len(cell.Formula) > 0 Then
        cell.Interior.ColorIndex = Range(cell.Formula).Interior.ColorIndex
      End If
    Next cell
  Next ws

End Sub

Basically on cell change it finds which cell B1 is looking at, gets the target cell's color and copies it. It does this across all sheets in the workbook.

I know it's a bit weird but serves it's purpose. Thanks everyone!

Community
  • 1
  • 1
Lee
  • 1,485
  • 2
  • 24
  • 44
  • The functions looks correct . Could you try changing `cellcolor.Interior.Color` to `Range("B1").Interior.Color` because at the moment it appears that you are trying to assign a color to the cellcolor variable which is a `long` value rather than a range – izzymo Mar 11 '15 at 08:46
  • @izzymo which functions look correct? In the second example the OP is trying to assign a value to the `.Interior.Color` property of a Long, shouldn't that be raising a few alarm bells here? Pretty sure this is raising a compile-time error but the OP simply failed to include that information in their question. Lee, I'm guess you're receiving an `invalid qualifier` error in your second attempt? brett gets right to the point though, Used-Defined Functions can't really affect the worksheet in this manner. – Aiken Mar 11 '15 at 08:51
  • @aiken good pick-up, fat fingered on my behalf! I meant http://stackoverflow.com/questions/8520732/i-dont-want-my-excel-add-in-to-return-an-array-instead-i-need-a-udf-to-change – brettdj Mar 11 '15 at 09:00
  • Thanks guys! I didn't realise that functions can't change cell colors! I've added the fix into the original question. Thanks again – Lee Mar 11 '15 at 09:22

0 Answers0