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.

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.

Try this: On Sheet 2, highlight cells A2 - A6

On the Task Bar,

Insert,

Name

DefineIn the

Define Namewindow

down the bottom underRefers To:

your range of cells should already be indicated: =Sheet2!$A$2:$A$6At the top under

Names In Workbook:simply give it a name,

likeMyopaClick OK

Now, on Sheet 1, highlight cell A2

On the Task Bar,

Format

Conditional FormattingIn the

Conditional Formattingwindow,

change theCell Value Is

toFormula IsEnter the formula:

=COUNTIF(Myopa,A2)=1

Click the Format button

Select the Patterns Tab

Select the color Green

Click OK

Click OKMIKE

Hi, You might want to use:

=COUNTIF(Myopa,A2)>0Then A2 will be green even if the value in A2 occurs more than once in the range A2:A6 on Sheet 2.

Regards

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?

Did you: On Sheet 1, highlight cell A2

On the Task Bar,

Format

Conditional FormattingIn the Conditional Formatting window,

change the Cell Value Is

to Formula IsEnter the formula:

=COUNTIF(Myopa,A2)>0

Click the Format button

Select the Patterns Tab

Select the color Green

Click OK

Click OKMake sure you have the Myopa name correct.

Try using the other formula:

=COUNTIF(Myopa,A2)=1

MIKE

Changing to equal 1 worked perfect. Thank you for your help, all of you.

Ask Your Question

Weekly Poll