Forum Discussion

AusVolleyball's avatar
AusVolleyball
Copper Contributor
May 30, 2024

Conditional Formatting based on two ranges of data (postcodes)

Hi All,

 

I'm looking to format one range of postcode data based on the values given ina range in another sheet. Im trying to match up businesses to postcodes, based on postcodes assigned to the businesses. See the screen shot below. In this example I am looking to highlight any postcodes in sheet 1, that are the same as the postcodes in sheet 2's data set.

 


I've been searching for a conditional formatting formula but haven't found any that work, please help!

 

  • AusVolleyball 

     

    Try to add this on the formulation of conditional formatting of the first cell of Sheet1:

     

    =ISNUMBER(XMATCH(A1;Sheet2!$A$1:$A$5))

     

    Assuming A1 is the first cell on Sheet1 and A1:A5 is the full range of Sheet2. Please note the absolute/relative referenes.

     

    Then, select the "Format Painter" on the Home tab of the ribbon and copy the format of this first cell to the rest of the cells in Sheet1.

Resources