Hi
You do this with Conditional Formatting.
You will finds loads of examples if you Google it. Exactly how you do it depends on which version of Excel you have, you have to experiment.
Del +++
>________________________________
>From: Sharron Puryear <sheizageek@yahoo.com>
>To: "ExcelVBA@yahoogroups.com" <ExcelVBA@yahoogroups.com>
>Sent: Monday, 7 November 2011, 21:03
>Subject: Re: [ExcelVBA] Counting Colored Cells in Excel 2010
>
>
>
>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]
>
>
>
>
>
[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