SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2234310%22%20slang%3D%22en-US%22%3EExcel%20-%20Conditional%20formatting%20-%20how%20to%20duplicate%20AND%20change%20the%20'Rule%20Cell%20Value'%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2234310%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20am%20creating%20a%20quality%20assurance%20process%20for%20my%20business.%20For%20example%2C%20I%20need%20to%20test%20an%20oven%20every%206%20months.%20I%20want%20to%20make%20sure%20that%20the%20oven%20heats%20to%20180C%20every%20time%20we%20test%20it.%20If%20the%20temperature%20does%20not%20reach%20180C%20I%20want%20this%20to%20be%20highlighted%20so%20we%20can%20address%20the%20issue.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20this%20I%20am%20trying%20to%20create%20conditional%20formatting%20to%20compare%202%20cell%20values%20(ie.%20I%20am%20to%20trying%20to%20find%20a%20way%20to%20highlight%20when%20the%20value%20in%20B2%20does%20not%20match%20the%20value%20in%20C2%20-%20with%20B2%20being%20January%20and%20C2%20being%20July).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20how%20to%20do%20one%20comparison%20(ie.%20Home%20-%20Conditional%20Formatting%20-%20Highlight%20Cell%20rules%20-%20More%20rules%20-%20'format%20only%20cells%20with%3A%20'cell%20value'%20'not%20equal%20to'%20'select%20desired%20cell'.%20)%20That's%20fine.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20problem%20is%2C%20I%20am%20wanting%20to%20do%20the%20same%20formatting%20for%20over%20100%20values%20in%20a%20quick%20way.%3C%2FP%3E%3CP%3Eie.%20Compare%20B2%20with%20C2%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB3%20with%20C3%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB4%20with%20C4%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB5%20with%20C5%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20have%20time%20to%20do%20conditional%20formatting%20100%2B%20times.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20tried%20copy%20and%20paste%20as%20well%20as%20duplicating%20the%20conditional%20formatting%20to%20other%20cells%2C%20but%20the%20problem%20is%20the%20new%20cells%20are%20always%20compared%20to%20a%20single%20cell%20(e.g.%20I%20want%20Cell%20C5%20to%20be%20compared%20against%20B5%20BUT%20it%20keeps%20being%20compared%20to%20B2%20-%20the%20original%20cell%20value%20rule).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20can%20I%20format%20100%2B%20cells%20in%20a%20quick%20manner%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHELP!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2234310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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.