Help Highlight both cell

[Trident_muh]

Sheet1 B2 and Sheet2 B2 has the same data, Now I want to highlight both cell if they are both the same, what formula should I use, using conditional formating

 

[🤖]

To highlight both cells if they contain the same data, you can use conditional formatting. Follow the steps below:

1. Select the range of cells you want to apply the conditional formatting to. In your case, select both Sheet1 B2 and Sheet2 B2.
2. Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
3. Select "New Rule".
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
5. In the "Format values where this formula is true" field, enter the formula:
=Sheet1!B2=Sheet2!B2
Note: Make sure to adjust the cell references if your data is in different locations.
6. Click on the "Format" button to choose the formatting you want to apply to the cells when the condition is met. For example, you can select a fill color or font style.
7. Click "OK" to close the "Format Cells" dialog box.
8. Click "OK" again to close the "New Formatting Rule" dialog box.

Now, both Sheet1 B2 and Sheet2 B2 will be highlighted if they contain the same data. You can test this by changing the data in one of the cells and observing the formatting change.

 

[Trident_muh]

It does not work, Is there any other way?

To highlight both cells if they contain the same data, you can use conditional formatting. Follow the steps below:

1. Select the range of cells you want to apply the conditional formatting to. In your case, select both Sheet1 B2 and Sheet2 B2.
2. Go to the "Home" tab in the Excel ribbon and click on "Conditional Formatting" in the "Styles" group.
3. Select "New Rule".
4. In the "New Formatting Rule" dialog box, select "Use a formula to determine which cells to format".
5. In the "Format values where this formula is true" field, enter the formula:
=Sheet1!B2=Sheet2!B2
Note: Make sure to adjust the cell references if your data is in different locations.
6. Click on the "Format" button to choose the formatting you want to apply to the cells when the condition is met. For example, you can select a fill color or font style.
7. Click "OK" to close the "Format Cells" dialog box.
8. Click "OK" again to close the "New Formatting Rule" dialog box.

Now, both Sheet1 B2 and Sheet2 B2 will be highlighted if they contain the same data. You can test this by changing the data in one of the cells and observing the formatting change.

how about this formula, =AND(COUNTIF($B$2:$B$100007,$B2),COUNTIF('Sheet2'!$B$2:$B$100007,$B2))