User-defined Function to change color of a cell

Question

I've seen many users asking questions trying to change the colors of cells using User-defined functions. I was always under the impression that it was not possible to do so. My understanding was that a user-defined function cannot change any properties of a cell except the value of the cell that contains the formula. Subs are what change cells themselves.

However, when playing around with some code to test this, I found that it's not always the case.
Using the simple code:

Function ColorCell(rng As Range)
If rng.Value = 1 Then
   ColorCell = False
Else
   ColorCell = True
   rng.Interior.ColorIndex = 3
End If
End Function

If I enter the function into a cell, I achieve expected results, no cells change colors. However, if I use the Formulas > Insert Function button and navigate to my formula to insert it this way, it does color the targeted cells.

How is this possible, and why did the function behave differently when entered in different ways?

EDIT: this was tested using Excel 2007

Answer 1

use this code...just replace sheet name and try

Sheets("sheet_name").range(j:j).clear

for j=2 to 15
if Sheets("sheet_name").Cells(j, 1).value=1 then

else

Sheets("sheet_name").Cells(j, 1).Interior.ColorIndex = 3
next j

Answer 2

As we all find out sooner or later, in user functions you can't access subs that change things in your spreadsheet directly.

But try this:

Dim ColorMeTarget As Range, ColorMeVal As Long

Public Function ColorMe(ByVal TargetRange As Range, ByVal ColVal As Long)
  Set ColorMeTarget = TargetRange
  ColorMeVal = ColVal
  ColorMe = ColVal
End Function

Public Sub ColorMeSub()
  Application.OnTime Now + TimeValue("00:00:05"), "ColorMeSub"
  If ColorMeTarget.Interior.Color <> ColorMeVal Then ColorMeTarget.Interior.Color = ColorMeVal
End Sub

If you run the sub first, it will constantly scan the static variables ColorMeTarget and ColorMeVal to see if there is a change. The function ColorMe will set these values. Some additional code is needed in case ColorMeTarget is not yet initialized.

If you get smarter, you could have the function first check to see if there is indeed a change and add the new coloring requests to a stack. Your reoccurring sub can then 'catch up', especially if you have many functions like this.

You can then even have all kinds of additional controls added to your function/macro--EVEN STUFF NOT COVERED BY THE LATEST VERSIONS OF 'CONDITIONAL FORMATING'!!! YAY!!!!

Something to try: In some of my automated macros, I am able to set OnTime through a function but cannot make it work here. It would be cleaner to have the function set the OnTime and not have a reoccuring sub that needs initializing.

Answer 3

I use Worksheet_Change event to detect value change in working range. Example.I want to do something when range A1:A5 has been change. I use below event.

Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(<Your working range>)) Is Nothing Then 'Define range that you want to do
       'Statement here
       ...

    End If
End Sub

When the range value has been change. It will execute your code.

And other way. Use Conditional Formatting.

Answer 4

could also try the non-script method of auto-coloring a cell based on the condition of the cell (aka Conditional Formatting):