wgwinn
10-18-2007, 08:45 AM
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
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