Excel - Conditional formatting - how to duplicate AND change the 'Rule Cell Value'

New Contributor



I'm am creating a quality assurance process for my business. For example, I need to test an oven every 6 months. I want to make sure that the oven heats to 180C every time we test it. If the temperature does not reach 180C I want this to be highlighted so we can address the issue. 


For this I am trying to create conditional formatting to compare 2 cell values (ie. I am to trying to find a way to highlight when the value in B2 does not match the value in C2 - with B2 being January and C2 being July).


I know how to do one comparison (ie. Home - Conditional Formatting - Highlight Cell rules - More rules - 'format only cells with: 'cell value' 'not equal to' 'select desired cell'. ) That's fine. 


My problem is, I am wanting to do the same formatting for over 100 values in a quick way.

ie. Compare B2 with C2

                     B3 with C3

                     B4 with C4

                     B5 with C5


I don't have time to do conditional formatting 100+ times. 


I have tried copy and paste as well as duplicating the conditional formatting to other cells, but the problem is the new cells are always compared to a single cell (e.g. I want Cell C5 to be compared against B5 BUT it keeps being compared to B2 - the original cell value rule).


How can I format 100+ cells in a quick manner?



4 Replies



All you need to do is...


Select the range B2:Cn where n is the last row and make a New Rule for conditional formatting using the formula given below and set the format as per your choice.


best response confirmed by Carla2404 (New Contributor)

Refer to the following screenshot to create a New Rule for conditional formatting...

New Rule.jpg

You're welcome @Carla2404! Glad it worked as desired.