background color change

[G2K]

Hi all,

i have been trying to change cell background color using following code but it's not been working. please help me to fix this problem.


Function ChangeColor(rng As Range, rngcolor As Range)
Dim cell As Range
Dim cellcolor As Long
cellcolor = rngcolor.Range("a1").Interior.ColorIndex
For Each cell In rng
If cell.Interior.Color = vbWhite Then
cell.Interior.ColorIndex = cellcolor
End If
Next cell
End Function


thanks in advance

 

[EducatedFool]

It works fine:

Function ChangeColor(rng As Range, rngcolor As Range)
    Dim cell As Range, cellcolor As Long
    cellcolor = rngcolor.Range("a1").Interior.ColorIndex
    For Each cell In rng
        If cell.Interior.Color = vbWhite Then
            cell.Interior.ColorIndex = cellcolor
        End If
    Next cell
End Function

Sub test()
    ChangeColor [d2:h6], [a2]
End Sub

 

[G2K]

thanks, but i want to call it on excel sheet like UDF

=changecolor(A1:A100,B1)

it's not working over there...

 

[EducatedFool]

It will not work over there...

UDF cannot change other cells or it's formatting.
(UDF has read-only access to other cells)

 

[G2K]

ok, is there any way to get desired output without opening VBA Editor