Can this be done with Excel 2007 VBA?

[dave]

I'd like to be change the background colour of a cell(s) depending on
conditions in other cells. In pseudo code eg
if(R1C == 10) then R5C6 = colorThisCell(blue);
(Am using pseudo code as I'm just started learning VBA.)

I have Walkenbach's book "Power Prog VBA for Excel 2007" and in it is
a statement...

"FUNCTION procedures used in worksheets must be passive. For example,
code within a FUNCTION procedure cannot manipulate ranges or change
things on the worksheet."

Amazingly the example he goes on to give is exactly the task I'd like
to achieve (above)! So looks like is not do'able.

Is there some other way to achieve this colouring task?
thanks

 

[Don Guillett]

Yes, a macro can do this
sub colorif()
if range("c1")=10 then activecell.interior.colorindex=5
end sub

or you can just use conditional formatting. Look in the help index

 

[dave]

Thanks Don. Seems I missed the meaning of the book's comments.
(So a Function cannot do it - but a macro can?)

 

[dave]

Just to follow up again - I just found your very reply saying "use
conditional formatting" about this task in that book!
Not of the sub() solution though.

Well, it is a 1000 page book and I'm new to all this.
thank again.

 

[Don Guillett]

Just to follow up again - I just found your very reply saying "use
conditional formatting" about this task in that book!
Not of the sub() solution though.

Well, it is a 1000 page book and I'm new to all this.
thank again.

Good luck in learning excel. It will be worth it. Always try to find
the ans yourself and then come here or other ngs for help.

 

[Jim Cone]

Further to Don's explanation...
A custom function called from a worksheet cell can only return a value to that cell (no formatting).
It cannot return values to any other cell.
A sub or function NOT called from a worksheet cell does not have that limitation.
--
Jim Cone
Portland, Oregon USA .
http://www.mediafire.com/PrimitiveSoftware .
(free and commercial excel programs)




"dave" <[email protected]>
wrote in message
news:[email protected]...