I have a workbook with multiple sheets. The main sheet pulls values from the other sheets for a calculation. The calculation is done in a macro routine. What I want to do is if any of the values retrieved from the other sheets are zero, I want to change the color of the cell. Any suggestions? I've tried a few different things, but no luck so far.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
jlargent,
There is no way you can change formats of a cell triggered by a UDF (as Bruintje stated above).
I can think of a workaround.... but do you need to do further calculations with the result of your formula?
If not, then the solution would be to add any character to the formula result in case of net being 0, and then use a conditional formatting which checks the first character of the cell.
Example:
Function netscore2(gross As Integer, net As Integer)
netscore2 = gross + net
If net = 0 Then netscore2 = "#" & CStr(netscore2)
End Function
Then in the conditional formatting, put this formula condition
=LEFT($A$1,1)="#" and apply the format you wish.
The reference $A$1 of course needs to be replaced by the right one...
That's the only possibility I see
Hello jlargent,
this question is open for more then 4 weeks
it is time to clean up or revitalize
if not stated otherwise
my recommendation for the moderator will be
-PAQ and refund
-this will be finalized by an EE Moderator
-with no further update (26.01.2003)
PLEASE DO NOT ACCEPT THIS COMMENT AS ANSWER
HAGD:O)Bruintje
posted by ToolzEE v1.0
Business Accounts
Answer for Membership
by: angelIIIPosted on 2002-10-21 at 04:43:25ID: 7351647
You have 2 options:
* set the conditional formatting of the cell in a way that for 0 value, it gets the color you want (this is completely outside of any macro...)
* in the macro, use some code like this:
dim r as Range
dim c as Range
'where are the cells that you want to color whenever a value is 0
set r = yoursheet.Range("A3:Z26")
for each c in r
if val(c.Value2) = 0 then
'Set the color to whatever you would like to
c.Interior.Color = vbGreen
else
'to clear any other setting:
c.interior.Color = vbWhite
end if
next
CHeers