Can I conditional format a circle

Question

I am creating an overtime board.  I want to input a value into one cell, VOT (voluntary overtime), and when I do I want another cell to contain a yellow circle.  If I input MOT (mandatory overtime), I want the circle to be red.  Is there a way to do this?  Right now I can conditionally format the whole cell, but I need a circle.  Is this possible?

Answer 1

I will suggest following solution -

1. Let's say your values are in A1:A10 and you want to display circles in B1:B10.
2. Put following formula in B1 and drag down to B10

=IF(A1="VOT",1,IF(A1="MOT",2))

3. Select B1 to B10, apply conditional formatting>New Rule>Format All Cells Based on Their values
4. In the Format Style, choose Icon Sets.
5. In Icon Style, Choose 3 circles as shown below
6. Click Show Icon Only.
7. Choose First Red and in second choose Yellow circle as in picture (It will change Icon Style to Custom. It doesn't make any difference). Third Red circle is redundant.
8. Choose Number in Type and put values in Value as in picture.

3

Answer 2

Sorry it took so long to get back to you.  I have not been able to access the site for the last 3 days. It simply was not responding when I tried to call up any page.

Since you are insisting on using shapes rather than the conditional symbols, here are a couple of articles that may  help

Programming Excel 2007 and Excel 2010 AutoShapes with VBA

http://peltiertech.com/WordPress/programming-excel-2007-2010-autoshapes-with-vba/

Changing background color of a shape using macro

http://answers.microsoft.com/en-us/office/forum/office_2010-customize/changing-background-color-of-a-shape-using-macro/a4656c13-3a1c-4d85-b9f9-7ade68c1f6aa

Answer 3

Thank you very much Vijay for your response.  The board I am creating will be a large posting that associates will be able to see from a distance, so the circle have to be larger than what normal condition formatting will allow.  However, your answer is very useful for my overall knowledge of working with excel and I appreciate it.

Answer 4

Thank you for your response.  Unfortunately, none of the suggestions helped.  I need the circle to be as large as possible within the cell, touching all four sides of the cell.  If I increase the font then the whole row gets bigger.  Therefore, the wingdings solution did not work...or at least I could not make it work.

Answer 5

The general concept you are talking about is called "conditional formatting".

Create Coloured Shapes

http://www.contextures.com/xlCondFormat03.html

I think this article has the answer to your question:

10 cool ways to use Excel's conditional formatting feature - 2007 2010

http://www.techrepublic.com/blog/10things/10-cool-ways-to-use-excels-conditional-formatting-feature/3166?tag=nl.e103

This page, in the "normal cell Formatting" section shows the old school approach to creating colored symbols

Conditional Formatting:http://www.mvps.org/dmcritchie/excel/condfmt.htm

This page has colored symbols and offers a example spreadsheet you can download

Excel Conditional Formatting -- Examples

http://www.contextures.com/xlCondFormat03.html

Excel 2007 Conditional Formatting – 4 Awesome Reasons I Love this Command

http://web.archive.org/web/20110114223803/http://www.excel-spreadsheet-authors.com/excel-2007-conditional-formatting.html/