Function to write the Interior Color

[Ann_BBO]

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.

[vba]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[/vba]

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]

Note that the above question are just simplify to bring out the general idea.

[mdmackillop]

A function can return the value for the cell. It cannot set any other cell property

[Paul_Hossler]

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]

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]

Correct. and calling a Sub from a function doesn't do the trick either!

[Cyberdude]

Boy, that's for sure. Some of the world's greatest minds have tried to circumvent that Excel rule without success.

[mikerickson]

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]

There ya go Sid.