Search code examples
excelexcel-formulaexcel-2007conditional-formatting

Change colour on row in one sheet if same value exists in second sheet

I have a sheet named Skruvdragare where cells B1-B500 contain serial numbers like: 123456789, 987654321 etc and a second sheet Låda 1 in which I will also put in some serial numbers (not necessarily the same numbers as in the Skruvdragare sheet).

If I put in a serial number, anywhere in Låda 1, which exists in Skruvdragare I want the row for this serial number to turn to red background and white text in the Skruvdragare sheet.

Solution

  • Use Conditional Formatting for this. But, as I've learned right now, the formula must not contain references to other sheets...so we work around this limitation by putting the formula into an auxiliary column in sheet2: 

    =NOT(ISERROR(MATCH($A1,sheet1!$A$1:$A$500,0)))

    and the conditional format just contains the formula

    =$D1

    if the aux column was "D". Copy that format to as many cells you like (except for the aux column!).