Change Cell Color

Microsoft Excel 2003 (full product)
June 6, 2010 at 12:51:20
Specs: Windows 7
I need to change a cell color when the data in the cell matches the data in a range of cels on another worksheet. EXAMPLE: Sheet 1 cell A2 is empty and the user types "OPA" into the cell. If the word "OPA" appears on Sheet 2 in column A cell 2, 3, 4, 5 or 6, then change Sheet 1 Cell A2 to green. If not leave as is. I would like to use conditional formatting. I know I will need to name the cells in a range, but I am not sure how to write the formula to have it look to see if A2 on Sheet 1 is an exact match for cells A2 through A6 on Sheet 2.

See More: Change Cell Color

Report •


#1
June 6, 2010 at 13:51:21
IF(logical_test,value_if_true,value_if_false)
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE.
For more info use HELP in your spreadsheet.

Report •

#2
June 7, 2010 at 19:12:06
Try this:

On Sheet 2, highlight cells A2 - A6

On the Task Bar,
Insert,
Name
Define

In the Define Name window
down the bottom under Refers To:
your range of cells should already be indicated: =Sheet2!$A$2:$A$6

At the top under Names In Workbook: simply give it a name,
like Myopa

Click OK

Now, on Sheet 1, highlight cell A2

On the Task Bar,
Format
Conditional Formatting

In the Conditional Formatting window,
change the Cell Value Is
to Formula Is

Enter the formula:

=COUNTIF(Myopa,A2)=1

Click the Format button
Select the Patterns Tab
Select the color Green
Click OK
Click OK

MIKE

http://www.skeptic.com/


Report •

#3
June 8, 2010 at 04:53:39
Hi,

You might want to use:
=COUNTIF(Myopa,A2)>0

Then A2 will be green even if the value in A2 occurs more than once in the range A2:A6 on Sheet 2.

Regards


Report •

Related Solutions

#4
June 11, 2010 at 09:51:10
I am sorry, if I posted this in the wrong area, i looked for the Excel area and did not realize, it was under a different heading. i did try the =COUNTIF(Myopa,A2)>0
answer. This was the first formula that was acceted under the conditional formatting, but when I typed in a correct answer, the cell did not change color. Any other suggestions or hints at what may be wrong?

Report •

#5
June 11, 2010 at 11:06:13
Did you:

On Sheet 1, highlight cell A2

On the Task Bar,
Format
Conditional Formatting

In the Conditional Formatting window,
change the Cell Value Is
to Formula Is

Enter the formula:

=COUNTIF(Myopa,A2)>0

Click the Format button
Select the Patterns Tab
Select the color Green
Click OK
Click OK

Make sure you have the Myopa name correct.

Try using the other formula:

=COUNTIF(Myopa,A2)=1

MIKE

http://www.skeptic.com/


Report •

#6
June 14, 2010 at 08:04:10
Changing to equal 1 worked perfect. Thank you for your help, all of you.

Report •

#7
June 14, 2010 at 08:19:27
Glad we could help.

MIKE

http://www.skeptic.com/


Report •


Ask Question