Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays "#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Try these

MsgBox Cells(1, 1).Interior.ColorIndex
Cells(1, 1).Interior.ColorIndex = 10

Mike

"Christian Schratter" wrote:

Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays "#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Well, I already tried that once, and just redid it to verify it, but
unfortuantely using ColorIndex instead of Color does not change anything. :-(

"Mike H" wrote:

Try these

MsgBox Cells(1, 1).Interior.ColorIndex
Cells(1, 1).Interior.ColorIndex = 10

Mike

"Christian Schratter" wrote:

Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays "#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Try this:

Sub test()

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i

End Sub

--
Steve

"Christian Schratter" wrote
in message ...
Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

First, a change in the color of a cell does not cause a re-calculate to occur
on the worksheet. If you changed the value in the cell then the function ill
be executed.

Second, You should not reference a worksheet cell from inside a function.
If you want to use the value in A1 then pass A1 as a parameter

Public Function testColor(Target as range)

MsgBox Target.Interior.Color 'does work - displays e.g. 255
Target.Interior.Color = 10 'does NOT work - displays "#VALUE!"
'in the calling cell
End Function

call function from spreadsheet with
=testcolor(A1)


Third, the code above will not work. A function can only return a value
(not a color change) to the cell where the function is located and not a
different cell. Sub can change any cell but must be manually executed or use
an event.

You best choice might be to use a worksheet change event. Not sure what you
are really trying to do.


"Christian Schratter" wrote:

Well, I already tried that once, and just redid it to verify it, but
unfortuantely using ColorIndex instead of Color does not change anything. :-(

"Mike H" wrote:

Try these

MsgBox Cells(1, 1).Interior.ColorIndex
Cells(1, 1).Interior.ColorIndex = 10

Mike

"Christian Schratter" wrote:

Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays "#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Explain what you are actually trying to do. Eg, what do you mean by
This function gets called in a cell ("=testColor()")

Does that return a colour or an attempt to apply a new colour.

If you are expecting the function to work after changing a format it won't,
UDFs do not respond to changes to the interface, such as format changes.

In 2007, best forget about using ColorIndex, use Theme Colours and their
TintAndShade variants or apply your own RGB colour.

Regards,
Peter T


"Christian Schratter" wrote
in message ...
Well, I already tried that once, and just redid it to verify it, but
unfortuantely using ColorIndex instead of Color does not change anything.
:-(

"Mike H" wrote:

Try these

MsgBox Cells(1, 1).Interior.ColorIndex
Cells(1, 1).Interior.ColorIndex = 10

Mike

"Christian Schratter" wrote:

Hello

I made a function in a new module with a function which looks like
this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA
function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Following code is now in the the module "Module1", which is part of the
VBAProject (colorTestXLS.xlsm) (which is a macro enabled xls file as you can
see):


-- CODE <--

Public Function testColor()
test
End Function

Sub test()
MsgBox ("In SUB")

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub


-- RESULT <--
If I call the testColor function from a cell in a worksheet ("=testColor()")
then I actually get the message box pop-up telling me "In SUB" but nothing
else. The value of the corresponding cell changes again to "#VALUE!".


"AltaEgo" wrote:

Try this:

Sub test()

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i

End Sub

--
Steve

"Christian Schratter" wrote
in message ...
Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

Hey Joel,

thanks for your input. Your explanation concerning function- and
sub-possibilties might be an explanation to the issue (although I wish it
would be different).

Actually I tried to manually implement conditional formating, given such a
table:

Product | old price | new price
apple 1 5
banana 10 7
computer 1000 800

I wanted to color the "new price" column depending if the old price was
higher or lower than the new price. At first I tried to do this with
conditional formating, but to my knowledge you can not set the rules for the
excel 2007 innate conditional formatting that way?!

Kind regards
Christian

"Joel" wrote:

First, a change in the color of a cell does not cause a re-calculate to occur
on the worksheet. If you changed the value in the cell then the function ill
be executed.

Second, You should not reference a worksheet cell from inside a function.
If you want to use the value in A1 then pass A1 as a parameter

Public Function testColor(Target as range)

MsgBox Target.Interior.Color 'does work - displays e.g. 255
Target.Interior.Color = 10 'does NOT work - displays "#VALUE!"
'in the calling cell
End Function

call function from spreadsheet with
=testcolor(A1)


Third, the code above will not work. A function can only return a value
(not a color change) to the cell where the function is located and not a
different cell. Sub can change any cell but must be manually executed or use
an event.

You best choice might be to use a worksheet change event. Not sure what you
are really trying to do.

Hey Peter

An explanation what I want to do is given in the post above.

The function should apply a new colour.

Kind regards


Explain what you are actually trying to do. Eg, what do you mean by
This function gets called in a cell ("=testColor()")

Does that return a colour or an attempt to apply a new colour.

The function should apply a new colour.

As I and others have mentioned you can't do that from a UDF. Better to look
at a Worksheet change event

Regards,
Peter T

"Christian Schratter" wrote
in message ...
Hey Peter

An explanation what I want to do is given in the post above.

The function should apply a new colour.

Kind regards


Explain what you are actually trying to do. Eg, what do you mean by
This function gets called in a cell ("=testColor()")

Does that return a colour or an attempt to apply a new colour.

I haven't used confdtional formating yet in 2007. Don't have it on my pc at
work. but in 2003 you can select Formula Is ( not cell is) and create a
formula to test the two values in the same row. then copy the formula down
the column using PasteSpecial and selecting Value.

"Christian Schratter" wrote:

Hey Joel,

thanks for your input. Your explanation concerning function- and
sub-possibilties might be an explanation to the issue (although I wish it
would be different).

Actually I tried to manually implement conditional formating, given such a
table:

Product | old price | new price
apple 1 5
banana 10 7
computer 1000 800

I wanted to color the "new price" column depending if the old price was
higher or lower than the new price. At first I tried to do this with
conditional formating, but to my knowledge you can not set the rules for the
excel 2007 innate conditional formatting that way?!

Kind regards
Christian

"Joel" wrote:

First, a change in the color of a cell does not cause a re-calculate to occur
on the worksheet. If you changed the value in the cell then the function ill
be executed.

Second, You should not reference a worksheet cell from inside a function.
If you want to use the value in A1 then pass A1 as a parameter

Public Function testColor(Target as range)

MsgBox Target.Interior.Color 'does work - displays e.g. 255
Target.Interior.Color = 10 'does NOT work - displays "#VALUE!"
'in the calling cell
End Function

call function from spreadsheet with
=testcolor(A1)


Third, the code above will not work. A function can only return a value
(not a color change) to the cell where the function is located and not a
different cell. Sub can change any cell but must be manually executed or use
an event.

You best choice might be to use a worksheet change event. Not sure what you
are really trying to do.

What you're trying to do is not allowed in Excel. You CANNOT change the
format of a cell with a user-defined function (UDF). That is why the msgbox
works (because it's not changing anything about the cell), but the attempt to
change the color bombs. The reason you get #VALUE is that your function
errors out before it returns. The error is probably occuring when you try to
set a cell interior color.

Also, a UDF generally should return a VALUE, e.g.:

Public Function testColor() as Long
test
testColor = 15 ' <-- This value should show up in the cell with
=testColor()
End Function


HTH,

Eric

"Christian Schratter" wrote:

Following code is now in the the module "Module1", which is part of the
VBAProject (colorTestXLS.xlsm) (which is a macro enabled xls file as you can
see):


-- CODE <--

Public Function testColor()
test
End Function

Sub test()
MsgBox ("In SUB")

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub


-- RESULT <--
If I call the testColor function from a cell in a worksheet ("=testColor()")
then I actually get the message box pop-up telling me "In SUB" but nothing
else. The value of the corresponding cell changes again to "#VALUE!".


"AltaEgo" wrote:

Try this:

Sub test()

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i

End Sub

--
Steve

"Christian Schratter" wrote
in message ...
Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.

I guess your (and Joel's) comments explain the reason for the error. While
it's always fine to have "evidence" why something happens, in this case it's
still a little bit sad.

Actually formatting a cell based on another cell would be a typical case for
conditional formatting in my opinion, and I'm surprised that Excel doesn't
provide some flexibility in this area.


As additional note: some kind of workaround for this issue is to make
another column where you calculate certain constant threshold values which
can then be conditionally formatted.
E.g.: with the 3 columns "Product", "old price", "new price" you can use
another column to calculate if the new price is above or below the old price,
and always display "above" or "below". Then you can apply the conditional
formatting to this column.

Kind regards, Christian



"EricG" wrote:

What you're trying to do is not allowed in Excel. You CANNOT change the
format of a cell with a user-defined function (UDF). That is why the msgbox
works (because it's not changing anything about the cell), but the attempt to
change the color bombs. The reason you get #VALUE is that your function
errors out before it returns. The error is probably occuring when you try to
set a cell interior color.

Also, a UDF generally should return a VALUE, e.g.:

Public Function testColor() as Long
test
testColor = 15 ' <-- This value should show up in the cell with
=testColor()
End Function


HTH,

Eric

"Christian Schratter" wrote:

Following code is now in the the module "Module1", which is part of the
VBAProject (colorTestXLS.xlsm) (which is a macro enabled xls file as you can
see):


-- CODE <--

Public Function testColor()
test
End Function

Sub test()
MsgBox ("In SUB")

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i
End Sub


-- RESULT <--
If I call the testColor function from a cell in a worksheet ("=testColor()")
then I actually get the message box pop-up telling me "In SUB" but nothing
else. The value of the corresponding cell changes again to "#VALUE!".


"AltaEgo" wrote:

Try this:

Sub test()

For i = 1 To 56
Cells(i, 1).Interior.ColorIndex = i
Next i

End Sub

--
Steve

"Christian Schratter" wrote
in message ...
Hello

I made a function in a new module with a function which looks like this:

Public Function testColor()

MsgBox Cells(1, 1).Interior.Color 'does work - displays e.g. 255
Cells(1, 1).Interior.Color = 10 'does NOT work - displays
"#VALUE!"
in the calling cell

End Function

This function gets called in a cell ("=testColor()").
The target would be to change the color of a cell with this VBA function.
Sadly setting the color does not work, while I can perfectly read the
color-code.

Where's the mistake here? I'm using Excel 2007.