Solved: Trying to set a cell color from a UDF _not_ a sub()

[wgwinn]

I have downloaded and tried the worksheet_change() routines for more then 3 conditionals; and thats works for making the color change to a cell.
However, im trying to integrate it all into one UDF that can be called from a cell as an `=ShowSchedule(CurrentCell) and make calculations dependent on the location. No matter what i do, i cnat seem to get the cell color to change.

I have tried the following things, with the following results:

worksheet_changed() in Sheet1 - Would make the color change to the edited cell; but would not (seem to) see changes to other cells based on the selected cell. As well, there is no clearly defined rnage for the cells i want scanned, so using Intersect to limit it's range is tedious at best.


Sub Both private and public. Running the Sub through 'Tools' -> 'Macro' works as expected, and makes the appropriate change.

However, Neither putting the code directly into the function nor calling the sub from the function would change the color. It will do any math asked of it, it will create any msgbox with any debug text as expected, but the color of the cell ends up being -4142, xlNone.

I've searched this forum and google for quite some time, but the only examples of color changing i can find all seem to be either sub()'s or worksheet_changed() functions. My security setting is at 'low' ; I cant find anything that would leed me to believe that udf's in Excel simply cant set colors while macro's can.

Please, is there something i should check? or am i jsut going about this all wrong ?


Example code:

This prints test no matter what, and changes colors when run from Tools - Macro menu

Public Sub Macro1()
MsgBox ("test")
Range("N33").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSemiGray75
.PatternColorIndex = xlAutomatic
End With
Range("N34").Select
With Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub

This code does not change any color, and neither does Macro1 from above change any colors - but it does print Test.

Function GC(Cell As Range, Target As Range)
Dim iColor As Integer
iColor = Cell.Interior.ColorIndex
Range("N30").Select
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
GC = iColor & Target.Address
Macro1
End Function

[Norie]

You can only use UDFs to return values, you can't use them for formatting or for putting values in other cells.

[wgwinn]

Well, I'd pretty much begun to think that .. but i couldnt find anywhere that said 'This wont work in.." Thank you for your time; I suppose i shall simply have to bash worksheet_change into something i can work with

[Bob Phillips]

Why not just use conditional formatting?

[wgwinn]

Why not just use conditional formatting?

That is, in fact, what I did use before I found out my company only has 2003 on the management systems. :banghead:

So I'm forced to move over to2k3, which obviously does not support more then 3, let alone the ~20 i was using.

The examples that i've found on the web do in fact work; for the single cell I edit text in. The other cells that update from that ( up to ~200) do _NOT_ seem to update colors when the values change.

Since I had to move to a macro anyway, I figured i'd remove the very lengthy and hard to read formulae from the main body of the sheet and write a macro for that, as well. I was doing all the 'This is what this cell should be' work there, I felt it made sense to go ahead and do the conditional decisions there - especially since it meant i didnt need to worry about watching the Range information for the worksheet_change function; as the cells arent a particularly tight group; i'll need eitehr about 10 ranges to check, or just let it run through all the cells.

I'd much prefer the option of upgrading my company to '07 and keeping my already done sheet; but, well, im not in Purchasing, so

[unmarkedhelicopter]

People have a great deal of difficulty keeping track of half a dozen colours and only about ten percent of the population can track 12 (and only with practice) (I don't mean tell the difference between colours I mean :- red means this, blue means that, yellow means etc etc.)
With code you can do more than 2003 default of 4 colors (you forgot the base colour) but I would really examine what you are doing first.

[wgwinn]

Oh, i do agree with you; as far as humans tracking colors go; but all that anyone will have to handle regularly is 5 or so, and one of those is 'default' and one is 'emergency'. Basically it's a daily schedule for about 50 people running 24/7 ; tracking shifts, breaks, and lunches for about ~75 people in 3 groups. All most people need to do is follow their color acroos the board for schedules, and managers need to go down the column for 'how many' of each color. But again, i dont make the color code, i jsut get the fun of putting it out.

[Bob Phillips]

You can in fact.

You can use the Worksheet Calculate event to test a value in a cell and colour another cell(s) accoridngly.

[david000]

I have downloaded and tried the worksheet_change() routines for more then 3 conditionals; and thats works for making the color change to a cell.

Did you download this one?



Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub