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

Do you trust smart speakers to not spy on you?

Discuss in The Lounge

Poll History