Forum Discussion
Conditional Formatting Excel Online
I'm trying to use conditional formatting on column A in sheet1 to highlight any cells that have the same data as any of the cells in column A in sheet2.
I've tried using =COUNTIF('sheet2'!$A:$A) as well as =Sheet1!$A:$A and some other variations, but I get the same two errors:
- The value you entered isn’t valid. Please try again using alternative values, punctuation, or symbols.
- Select a single cell or use a function like =SUM(A1:A5)
Georgie13 Please make sure the Rule Type is set to "Formula" and use the following function in the formula input box as shown below.
=ISNUMBER(MATCH(A1,Sheet2!$A:$A,0))Hope this helps,
Dexter
5 Replies
Georgie13 Please make sure the Rule Type is set to "Formula" and use the following function in the formula input box as shown below.
=ISNUMBER(MATCH(A1,Sheet2!$A:$A,0))Hope this helps,
Dexter
- That's worked perfectly! thanks Dexter.
- The data in the columns won't be just numbers as they are part numbers so will have a mix of let's and numbers. Will the isnumber formula still work?
Georgie13 as long as they are consistent between the two sheets, yes. Meaning if a number is entered as an actual number on sheet one, the conditional formatting will work if it is also entered as a number on sheet 2. It will not work if the same value is entered as text on one sheet but not the other. Let me know if this is the case.
