Range Interior issues

GabePo

New Member
Joined
Nov 2, 2011
Messages
4
This is the code I'm using. Nothing complicated...

Code:
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:

Code:
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!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
This is the code I'm using. Nothing complicated...

Code:
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:

Code:
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

New Member
Joined
Nov 2, 2011
Messages
4
I'm not sure that will be useful but here it is:

Content of cell K13:

Code:
=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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 2, 2011
Messages
4
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

New Member
Joined
Nov 2, 2011
Messages
4
Re: Range Interior issues (SOLVED)

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

Code:
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!
 
Last edited:

Forum statistics

Threads
1,144,566
Messages
5,725,021
Members
422,589
Latest member
JohnnyBravo1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top