Format cell using Validation rule

[czanet]

Good day, Trying to find a quick and easy solution...looking for som
assistance.

I want to create a form for ACtion items (as in from a meeting) an
have a drop down box that allows users to choose status of Red, Yello
or green...

I thought using the Validation--List function might work...but alas..i
did not.

Any thoughts?

Thank

Attachment filename: try.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=53245

 

[DDM]

Czanet, a combination of Data Validation and Conditional Formatting will do
it. First, set up the validation, where the cell will accept Red, Yellow, or
Green. You can use a separate list or you can just type in the values in the
Source field in the Data Validation dialog box. Then, set up the conditional
formatting (Format > Conditional Formatting). Condition 1: Cell value is
equal to Red. Format: Font color = Red; Cell shading = Red. Repeat where
Condition 2 = Yellow and Condition 3 = Green.

For help with conditional formatting, look here:
http://www.contextures.com/xlCondFormat01.html.

 

[Jason Morin]

MVP Debra Dalgleish has some nice step-by-step
instructions on implementing Data Validation on her
website. It may help you:

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

HTH
Jason
Atlanta, GA

 

[Gbonda]

Any easy way would be to use conditional formatting.


Format > Conditional Formating

Create 3 conditions and change the font and pattern to the same color.

use 1,2, and 3. Choose a different color for each number.


Gre

Attachment filename: color cells.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=53247

 

[czanet]

Nice and easy...thanks...

one more question...can you apply the formatting to adjacent cell
based on the status in one cell....for example, if I select 1 (green
can I have the text in adjacent column change color?

Thank

 

[Max]

One way to effect ..

Clear the data validation [DV] in cells G16:G19

Amend G16 to read as : "Select color"

Input the words "green", "red" and "yellow"
in G17:G19
--
Select D5

Click Data > Validation and
change the Source to: =$G$16:$G$19

Click OK

(This is to capture G16 as the "label" in the DV drop list, as a user-aid)
--
With D5 still selected

Click Format > Conditional Formatting

Make the settings:

Condition 1
-------------
Cell Value Is | equal to | green

Click Format button and format *both*
the cell fill (pattern tab) and the font color (Font tab) to green
[ This will mask the words ]

Click OK

Click Add >> (adds condition 2)
Repeat similar steps as for condition 1 to add for "red"

Click Add >> (adds condition 3)
Repeat similar steps as for condition 1 to add for "yellow"

Click OK
--

Now test out the DV in D5

If you select "green" from the drop list,
the cell will be filled "green" color, and so on

(the words "green", etc will be masked as
we had earlier formatted the font in the same color as the cell fill)

 

[Gord Dibben]

In your Data Validation>List box type Green,Red,Yellow(note the commas must be
entered)

Select C5 and Format>Conditional Formatting and Condition1 Cell Value is:
="green" Format to Green color.

"Add" and do same for Condtion2 and ="red"

Same for Condition3 and ="yellow"

OK your way out.

With C5 still selected Format>Cells>Number>Custom enter ;;; and OK

Now click on drop-down arrow and select from the Green,Red,Yellow list.

C5 will turn color.

Gord Dibben Excel MVP

 

[Max]

Same principle .. conditional formatting

Suppose the data validation drop list is in D6

Select the targeted cell, say C6
(target cell need not be one adjacent to D6)

Click Format > Conditional Formatting

Make the settings:

Condition 1
-------------
Formula Is | =$D$6=1

Click Format button and format the font color (Font tab) to green

Click OK

 

[czanet]

Thanks Gord, Max, Jason, DDM..very helpful.

I have never used this forum before...but will be a regular goin
forward.

Thanks again.

 

[Earl Kiosterud]

Cza,

Yes, you can have several cells change formatting based on one cell. If you
want A2:E2 to change based on A2, select A2:E2, with the active (white) cell
A2. Now use a formula like

=$A2="red"

or whatever your condition is. This formula will be "copied" to the other
selected cells, and the A will not change because of the absolute cell
reference ($). You can select any of them, Format - Conditional Formatting,
and you'll see the changed formula.

=$A2="red" =$A2="red" =$A2="red" =$A2="red" =$A2="red"

If you selected other rows (so A3:E3 is formatted via A3, etc.) the row part
(the 2) will adjust, because that part of the cell reference is relative (no
$)to wit:

=$A3="red" =$A3="red" =$A3="red" =$A3="red" =$A3"red"
in then next row, etc.

Slick, eh?