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.
Cell Color Function

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

EDIT: this was tested using Excel 2007

I don't know the answer to this, but notice that if you change the cell values, it will not change the coloring even in the bottom case. So if you change cell A11 to 1 the cell remains red. — OpiesDad, May 29 '15 at 21:18
Correct, there's no updating of the function itself. Except if you go back and re-use "Insert Function," it *will* update the value/color. — TMH8885, May 29 '15 at 21:20
`I found that it's not always the case.` True :) Have you seen [This](http://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) — Siddharth Rout, May 29 '15 at 21:31
The only simple explanation that I can think of is that when you enter the function in the worksheet directly, it is calculating in `Edit` mode (Formula bar is still active). And in that "Edit" mode Excel's other features come to a halt. In your case it not the case per se. The formula bar is also not accessible which means excel is not in the same "Edit" mode. Well this is my understanding and I could be wrong :) — Siddharth Rout, May 29 '15 at 21:37
This is interesting, although there are probably ways to achieve your goals without trying to hack the intended limitations of functions, like conditional formatting. — n8., May 29 '15 at 21:38
@SiddharthRout I had not seen that yet, very interesting stuff. I didn't see this specific method listed on the Google site linked. My best guess is that it's very similar to using the "Evaluate Formula" method in Excel's eyes. — TMH8885, May 29 '15 at 21:38
@n8. I wasn't even trying to achieve anything with this, just wanting to test Excel's limitations :) — TMH8885, May 29 '15 at 21:39

score 4 · Answer 1 · answered Jun 09 '15 at 12:37

4

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

answered Jun 09 '15 at 12:37

NikhilP

1,508
14
23

score 1 · Answer 2 · answered Jul 09 '15 at 00:11

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.

score 0 · Answer 3 · answered Jul 02 '15 at 02:06

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.

score 0 · Answer 4 · answered Jul 21 '15 at 22:54

0

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

highlight desired range & click "conditional formatting from the toolbar

use either an established rule or make a new rule

note options on rule types; I selected one to simply format the range based on cell contents. You can format based on a formula as well. Set the conditions to test ... in this case I said the condition is when a cell value is more than 5. Then click on format and set the desire format whether it be a font change or shading of the cell, etc. Here I selected to shade the cell a color of green when above 5 Here is the result. You can delete the contents of the cells without deleting the conditional formatting set for the cells.

answered Jul 21 '15 at 22:54

shane

134
1
1
10

User-defined Function to change color of a cell

4 Answers4