User-defined Function to change color of a cell

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

Sheets("sheet_name").range(j:j).clearfor j=2 to 15if Sheets("sheet_name").Cells(j, 1).value=1 thenelseSheets("sheet_name").Cells(j, 1).Interior.ColorIndex = 3next j

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 LongPublic Function ColorMe(ByVal TargetRange As Range, ByVal ColVal As Long)  Set ColorMeTarget = TargetRange  ColorMeVal = ColVal  ColorMe = ColValEnd FunctionPublic Sub ColorMeSub()  Application.OnTime Now + TimeValue("00:00:05"), "ColorMeSub"  If ColorMeTarget.Interior.Color <> ColorMeVal Then ColorMeTarget.Interior.Color = ColorMeValEnd 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.

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 IfEnd Sub

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

And other way. Use Conditional Formatting.