Forum Discussion

Ahmed_Ezzedin's avatar
Ahmed_Ezzedin
Copper Contributor
Jan 12, 2022

Conditional formatting

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

excel
Formulas and Functions

4 Replies

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Jan 12, 2022

    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..

    • Ahmed_Ezzedin's avatar
      Ahmed_Ezzedin
      Copper Contributor
      Jan 12, 2022
      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.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Jan 13, 2022

        Ahmed_Ezzedin 

        Does this work?

         

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

         

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

Resources