MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

function to change the color of the cell

  Asked By: Craig    Date: Jan 03    Category: MS Office    Views: 1083

I'm trying to write a function that can change the color of the cell
that calls it. Can anyone tell me why I can read the ColorIndex, but
not set it? Help file says it is read/write, but it appears to be
locked out from writing in a Function.

Function Ctest()
'set color
With Application.Caller.Interior
.ColorIndex = 4
.Pattern = xlSolid
End With
'read back color
Ctest = Application.Caller.Interior.ColorIndex
'why doesn't the color get set???
End Function



2 Answers Found

Answer #1    Answered By: Marshall Bryant     Answered On: Jan 03

Correct; because it's a function  which is designed only to return a
value and not carry out any actions. Even if a sub is called from a
function, the action doesn't seem to get carried out.

What are you trying to do?

Answer #2    Answered By: Jae Williams     Answered On: Jan 03

I thought it would be cool if a custom worksheet function  could
include the equivalent of conditional formatting. That would save a
lot of time compared to setting up conditional formatting as a
separate step. Too bad Excel lets you determine the calling cell's
address, but not really make much use of it.

Didn't find what you were looking for? Find more on function to change the color of the cell Or get search suggestion and latest updates.