Alternatively, how can I change the color of a cell based on the entry of another cell? I have three columns, first column is Boat Name, Second column is Passed, Third Column is Failed. If second column is marked with an X then the cell in the first column turns Green, if an X is placed in the Third column, then the cell in the first column turns Red.
Boat Passed Failed
Boat1 X
Boat2 X
Boat3 X
Thanks
Sharron
________________________________
From: "david.smart@ubs.com" <david.smart@ubs.com>
To: ExcelVBA@yahoogroups.com
Sent: Wednesday, November 2, 2011 8:21 PM
Subject: RE: [ExcelVBA] Counting Colored Cells in Excel 2010
I assume you have the COLORCOUNT() call in a formula?
In order for it to be invoked, the recalculation event would need to
re-evaluate that cell. But a colour change doesn't cause a
recalculation as it doesn't change anything that formulas can use
directly.
I don't think that adding Application.Volatile in your function will
help in this case either, but please give it a go, as I'm only 50%
confident of this.
See
http://msdn.microsoft.com/en-us/library/aa213653%28v=office.11%29.aspx
Note also that Excel 2007(?) and later allow you to count cells by
colour directly, I believe.
Regards, Dave S
________________________________
From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On
Behalf Of sheizageek
Sent: Thursday, 3 November 2011 04:04
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Counting Colored Cells in Excel 2010
I am using the following code to count cells in a given range that are
of a color. The code works fine, but when I change colors in the range,
the counted numbers do not change even when I refresh all from the
ribbon. Does anyone know how to "fix" this?
Thanks
Function COLORCOUNT(varRange As Range, varColor As Range)
Dim cell As Range
For Each cell In varRange
If cell.Interior.ColorIndex = varColor.Interior.ColorIndex Then
COLORCOUNT = COLORCOUNT + 1
End If
Next
End Function
----------
Visit our website at http://www.ubs.com
This message contains confidential information and is intended only
for the individual named. If you are not the named addressee you
should not disseminate, distribute or copy this e-mail. Please
notify the sender immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system.
E-mails are not encrypted and cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The sender
therefore does not accept liability for any errors or omissions in the
contents of this message which arise as a result of e-mail transmission.
If verification is required please request a hard-copy version. This
message is provided for informational purposes and should not be
construed as a solicitation or offer to buy or sell any securities
or related financial instruments.
UBS reserves the right to retain all messages. Messages are protected
and accessed only in legally justified cases.
[Non-text portions of this message have been removed]
[Non-text portions of this message have been removed]
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar