SOLVED

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

New Contributor

Hi,

 

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?

 

HELP!

4 Replies

@Carla2404 

 

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.

 

=B2<>C2
best response confirmed by Carla2404 (New Contributor)
Solution

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.