Conditional formatting

[Ahmed_Ezzedin]

Hi everyone,

I need to create a conditional formatting to cells A21 to A39. I need them to change colors when certain values are entered. I named those values Mylist, but the problem is that these values (list) is on a different worksheet (sheet2). I tried using the =SUM(COUNTIF formula but I can't get it to work. Can anyone help me please.

Thanks,

Ahmed

[#2]

@Ahmed_Ezzedin 

Select A21:A39.

A21 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=ISNUMBER(MATCH(A21,Mylist,0))

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again..

[#3]

Hi Hans,

Thanks for your help, but it still not working. I'm not sure if I need to change something in the formula due to having the conditional values (mylist) in sheet2 not sheet one (where A21 is located). Those values are J16 to J24 in sheet2.

[#4]

@Ahmed_Ezzedin 

Does this work?

=ISNUMBER(MATCH(A21,Sheet2!$J$16:$J$24,0))

If not, please attach a sample workbook demonstrating the problem.

[#5]

Thanks Hans for your help, and sorry for not replying sooner. I ended up doing each cell separately and it worked.