Color a cell based on another cell... with a function

scottcolbury

New Member
Joined
Dec 6, 2005
Messages
45
Hi all,

I'm trying to write a worksheet function that will look at a cell on another worksheet, and then bring back both the value and color of the first cell.

For instance... I'm hoping to write something like this.
=GetColor(Range)

If I entered =GetColor(Sheet1!A1) in cells B2 on sheet2 then I would get both the value and color of Sheet1!A1 in Sheet2!B2.

I did some searching on the net and found ways to sum values based on colors, but nothing quite like this.

Here's the start of my code:

Code:
Function GetColor(InputCell As Range) As Double
Dim ColorRange As Integer

Application.Volatile True

    InputVal = InputCell.Value
    ColorRange = InputCell.Interior.ColorIndex

    GetColor = InputVal
    GetColor.Interior.ColorIndex = ColorRange
    
End Function

Obviously the following line doesn't work

GetColor.Interior.ColorIndex = ColorRange

Thanx in advance.
s_c
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Scott

I'm afraid you can't use a User Defined Function in a worksheet to alter the format of a cell.
 

Forum statistics

Threads
1,136,354
Messages
5,675,292
Members
419,559
Latest member
BraytonM

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