Function to set the color to a cell
Christian
I have several sheets in a workbook having cells with background color (green, yellow, red) and would like to have a summary sheet
that display the color used.
Is it possible to refer from one cell to another and get the cell to display the color?
For example:
A1 has background color green
in B1 I would enter =colorfunction(A1) and then cell B1 would also have a green background.
If the color changes in A1, B1 is immediately updated with the new background color.
If it's not possible with a build-in function would it then be possible to do this with a user function and how the heck would I do
that?
I've been trying something like this but can't quite get it to work
Private Function ColorFunction(RefCell) As Variant
' Returns the backgrond color of the cell in the formula
Dim InCell As Range
Dim OutCell As Range
Dim myColor As Variant
InCell = Cells(RefCell)
Set myColor = InCell.Application.Cells.Interior.Color
End Function
any ideas will be greatly appreciated
- Chr
Harald Staff
You are facing two limitations here. No, three.
1 There is no built-in function to read a cell's color. So you must write
it.
2 A function called from a cell can not color the cell, just return a value.
3 Coloring a cell does not trig a "calculate" event, if you can overcome
limitation 1 and 2 you still have to recalculate manually by F9 or by a
macro.
It's quite common to use color as information (like green=vacation,
red=overtime) but excel is not built for that and wil not handle it well.
You would be far better off using text and/or numbers as information.
Chip has a page on colors that may get you a little further if you choose
not to take my advice (I won't be hurt by that :-)
http://www.cpearson.com/excel/colors.htm
HTH. Best wishes Harald
"Christian" <christian...@mobilixnet.dk> wrote in message
news:epNsOOh5...@TK2MSFTNGP09.phx.gbl...
J.E. McGimpsey
their calling cells. They can't affect formatting, including background
color.
Unfortunately, changing the cell color doesn't fire any events, either,
but if you can live with updating whenever there's a selection change,
then you could put this in the worksheet code module:
Private Sub Worksheet_SelectionChange(ByVal Target as Excel.Range)
Range("B1").Interior.ColorIndex = _
Range("A1").Interior.Colorindex
End Sub
In article <epNsOOh5...@TK2MSFTNGP09.phx.gbl>, Christian
Christian
Thanks for the thorough reply.
Would it be possible to construct a macro that would read the color from the source cell and apply it to the target cell?
The idea would then be that.
Sheet1.A1 has background color green
Sheet2.A1 has the formula "=sheet1!A1"
The macro should update Sheet2.A1 to have the same color as source (Sheet1!.A1) and best would of course be that the macro doesn't
select each cell.
The macro could then be run in the summary sheet. The summary sheet will not have any other formula's than the one that refers to
the source cell for coloring.
Any ideas on how to do that?
- Chr
"Harald Staff" <harald...@eunet.no> wrote in message news:etzpYuh5...@TK2MSFTNGP11.phx.gbl...
Harald Staff
Assuming that A1 doesn't go into A1 all the time but is formula dependant.
This is a little worse, since the "Precedents" variable doesn't work across
worksheets. A solution is to loop all cells, strip each formula to find the
sheet name (between = and !), here "Whatever", and the address (just after
!), here "ThisToo", and do something like
Sheets(2).Cells(Row#, Column#).Interior.ColorIndex = _
Sheets("Whatever").Range("ThisToo").Interior.ColorIndex
If you want me to write it you'll have to wait a little, I'm off to go
skating with my daughter. But I'm sure you'l figure it out in the meantime.
Best wishes Harald
"Christian" <christian...@mobilixnet.dk> wrote in message
news:uAF6VCi5...@TK2MSFTNGP12.phx.gbl...
Christian
Thanks for the suggestion. I just got it working by getting some inspiration from
http://j-walk.com/ss/excel/tips/tip07.htm
These two macro's together seems to do the trick and really fast as well
Sub GetColor()
If TypeName(Selection) <> "Range" Then Exit Sub
' If one cell is selected, check it and exit
If Selection.Count = 1 Then
Call CheckCells(Selection)
Exit Sub
End If
On Error Resume Next
' Check the cells with formulas
Call CheckCells(Selection.SpecialCells(xlFormulas, 23))
End Sub
Sub CheckCells(CurrRange As Range)
For Each cell In CurrRange
If cell.Formula <> "" Then
cell.Cells.Interior.Color = Range(cell.Formula).Interior.Color
End If
Next cell
End Sub
Thanks again for all the help!
- Chr
"Harald Staff" <harald...@eunet.no> wrote in message news:Ot4Lbxi5...@TK2MSFTNGP09.phx.gbl...
Christian
I managed to create a macro using the your suggestion to get the cell color updated.
See other disc.thread with Harald for details.
- Chr
"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message news:090320030222449877%jemcg...@mvps.org...
Harald Staff
Cool. Neat new trick, thanks.
Best wishes Harald
"Christian" <christian...@mobilixnet.dk> wrote in message
news:#gdFh3i5...@TK2MSFTNGP11.phx.gbl...