Conditional formatting cells with values that dont match

Question

Hi All,

I have a data set of odometer reading for cars.

Column B = Start figure Column C = End Figure

and i have several rows

I want cell in Column B to highlight if the in the previous row/ Column C is different

I can get this to work but only on the cells im using in the formula

I want it to work the whole length of my data set.

how do i get this formatting to work on my whole document?

Example

Cell B3 high lights if C2 is different

Cell B7 high lights if C6 is different

Regards

Kelly

Answer 1

Hi Kelly,

Welcome to Microsoft Community.

Since Excel does not have a built-in function to directly retrieve the last row of the current worksheet, it is not straightforward to dynamically and automatically determine the length of the dataset. You may consider manually updating the range for conditional formatting or setting the range accordingly.

Select your dataset range, for example, B3:B500. Please note that you should not start from B2 because the first row is the header row, and B2 does not have a "top-right" cell to compare with. B500 is a simulated endpoint for a larger dataset. If your dataset exceeds 5000 rows, you can extend it to B1000, B2000, and so on.

Create a new conditional formatting rule > "Use a formula to determine which cells to format" > Enter the following formula:

=AND(B3<>"", B3<>C2)

Choose your desired highlight color and save the rule.

I hope this works effectively for you as well.

If you have any other questions or want to share more context, please let me know in your reply!

Best Regards,

Thomas C - MSFT | Microsoft Community Support Specialist

Answer 2

Hi Kelly,

I apologize for the late reply. There have been some issues with the community's notification system recently, and I did not initially receive your email alert. But I'm back now.

I'm glad that the previous response has resolved your issue. If my previous responses were helpful to you, please select 'Yes' under the response that assisted you.

It will highlight the response and make it easier for other users encountering the same issue to find the answer through search engines. Thank you for your cooperation.

The community follows the principle of one question per thread. Therefore, I had to split your new question into a new thread. You can click the link to go there: Automating Data Transfer Between Sheets: Sorting Fuel Litres into - Microsoft Community

I have understood your other question and have already formulated the solution. I will share the solution to the new question in the new thread after I finish writing this reply.

I'll see you there.
Thomas