PDA

View Full Version : Function to write the Interior Color



Ann_BBO
05-26-2010, 02:50 AM
Hi All,

One looks like a simple question but i cannot solve it.

Basically, i have written a self-defined function which is write the name of color to fulfill the interior color into cell.

Public Function CoIndex(str As String) As Variant
Select Case LCase(str)
Case "red": CoIndex = 3
Case "yellow": CoIndex = 6
End Select
ThisCell.Interior.ColorIndex = CoIndex
End Function

In the above function, i have defined the actual color index.
For example, if i type the formula in Cell A1=CoIndex("red"), then i want the cell shading in Cell A1 will be a red color.

Now the result has a error if it has this sentences "ThisCell.Interior.ColorIndex = CoIndex"
If i crossout this sentences, it will return the number of colorindex.

It is Interesting in this findings.

Thanks,
Ann

Ann_BBO
05-26-2010, 03:15 AM
Note that the above question are just simplify to bring out the general idea.

mdmackillop
05-26-2010, 04:25 AM
A function can return the value for the cell. It cannot set any other cell property

Paul_Hossler
05-26-2010, 04:27 AM
A function can not change the worksheet like that

It can only return values (i.e. the ColorIndex)


Possibly using Conditional Formatting, or the Worksheet_Change event would work, but that's more complicated

Paul

Ann_BBO
05-26-2010, 04:39 AM
Thanks MD and Paul.

To conclude that, for any self-defined function, we can only return the value (i.e. text and number) rather than any other property (e.g. change font color, bold function...). Is it correct?

If we really want to achieve the above purpose, we can only to write the Sub Marco to instead of the function. Please confirm my understanding.

Thanks All
Ann

mdmackillop
05-26-2010, 05:32 AM
Correct. and calling a Sub from a function doesn't do the trick either!

Cyberdude
05-26-2010, 05:03 PM
Boy, that's for sure. Some of the world's greatest minds have tried to circumvent that Excel rule without success. :motz2:

mikerickson
05-26-2010, 06:17 PM
By circumvent, do you mean like this.
Put this in a normal module

Public CellsToColor As New Collection
Public ColorOfCells As New Collection

Function CellOfColor(value_to_show As Variant, _
Optional color_index As Long = xlNone, _
Optional range_to_color As Range, _
Optional color_self = True) As Variant

CellOfColor = value_to_show

If color_index < 1 Or 56 < color_index Then color_index = xlNone

If color_self Then
With Application.Caller
On Error Resume Next
CellsToColor.Add Item:=.Cells, key:=.Address(, , , True)
ColorOfCells.Add Item:=color_index, key:=.Address(, , , True)
On Error GoTo 0
End With
End If

If Not range_to_color Is Nothing Then
With range_to_color
On Error Resume Next
CellsToColor.Add Item:=.Cells, key:=.Address(, , , True)
ColorOfCells.Add Item:=color_index, key:=.Address(, , , True)
On Error GoTo 0
End With
End If

End Function

And this in the ThisWorkbook code module
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim oneCell As Range
If CellsToColor.Count > 0 Then
On Error Resume Next
For Each oneCell In CellsToColor
oneCell.Interior.ColorIndex = ColorOfCells(oneCell.Address(, , , True))
Next oneCell
On Error GoTo 0
End If
Set CellsToColor = Nothing
Set ColorOfCells = Nothing
End Sub
In the attached, C1 holds the formula
=CellOfColor($A1+1, IF(B1="x",33, IF(B1="y",39,0)))
so that the value shown in the C1 is A1+1,
if B1="x", C1's interior color index is 33,
if B1="y", C1's interior color index is 39.
if B1 contains any other value C1 has xlNone as its interior color.

Entering a range for the optional range_to_color argument will color that other range.
Setting the optional color_self to FALSE will color the range_to_color, but not the cell holding the formula.

The trick is that
1) while a UDF cannot change a cell's color*, the Calulate event can.
2) a UDF can change a Public variable (a collection in this case) which can be used to "pass arguments" to the Calculate event
3) the Calculate event runs after a UDF.


*-This restriction applies only when the UDF is called from a worksheet. A UDF called from a VB routine can change a cell's color. (i.e. If Application.Caller is a range object then the UDF can not change a cell's color, otherwise it can.)

Aussiebear
05-28-2010, 10:06 PM
There ya go Sid.