Range Interior issues

[GabePo]

This is the code I'm using. Nothing complicated...

Public Function setRangeColor(fromCellAddress As String, toCells As Range)

    Dim fromCell As Range
    
    Set fromCell = Range(fromCellAddress)
    toCells.Interior.ColorIndex = fromCell.Interior.ColorIndex

    setRowColor = fromCell.Interior.ColorIndex

End Function

Now the macro crashes at:

toCells.Interior.ColorIndex = fromCell.Interior.ColorIndex

I have absolutely no error report and have tried to set all of the cells in the toCells range individually with the same result. If any of you can help it would be great!

 

[Rick Rothstein]

This is the code I'm using. Nothing complicated...

Public Function setRangeColor(fromCellAddress As String, toCells As Range)
 
    Dim fromCell As Range
 
    Set fromCell = Range(fromCellAddress)
    toCells.Interior.ColorIndex = fromCell.Interior.ColorIndex
 
    setRowColor = fromCell.Interior.ColorIndex
 
End Function

Now the macro crashes at:

toCells.Interior.ColorIndex = fromCell.Interior.ColorIndex

I have absolutely no error report and have tried to set all of the cells in the toCells range individually with the same result. If any of you can help it would be great!

Show us the line of code you are using to call this function.

 

[GabePo]

I'm not sure that will be useful but here it is:

Content of cell K13:

=setRangeColor(ADDRESS(MATCH(G13,$C$3:$C$10,0)+2,COLUMN($B$1),4),A13:J13)

G13 is a dropdown list from which the arguments are in $C$3:$C$10 and the legend is in column B.

 

[Rick Rothstein]

Ah, you are attempting to use the function on a worksheet as a UDF (user defined function). Formulas, whether constructed from built-in functions or UDFs, cannot change anything other than the displayed value in the cell the formula is assigned to. Here your UDF is attempting to change a color of a cell... a formula (that your UDF is part of) is not allowed to do that. You can do it with a macro or event code, but not by means of a formula.

 

[GabePo]

Thanks for the valuable info. Could you guide me with an "even based" solution where the trigger would be making a choice in the drop lists...

 

[GabePo]

Re: Range Interior issues (SOLVED)

I've got it! For future references here's my solution:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim row As Double
    Dim fromCell As Range
    Dim targetCells As Range
    Dim targetAddr As String

    If Not Intersect(Target, Range("G13:G2500")) Is Nothing Then
        row = Application.WorksheetFunction.Match(Target, Range("$C$3:$C$10"), 0) + 2
        Set fromCell = Range("B" & row)
        targetAddr = "A" & Target.row & ":J" & Target.row
        Set targetCells = Range(targetAddr)
        targetCells.Interior.ColorIndex = fromCell.Interior.ColorIndex
    End If

End Sub

Good luck people!