Forum Discussion
Excel - Conditional formatting - how to duplicate AND change the 'Rule Cell Value'
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!
Refer to the following screenshot to create a New Rule for conditional formatting...
4 Replies
- Subodh_Tiwari_sktneerSilver Contributor
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- Subodh_Tiwari_sktneerSilver Contributor
Refer to the following screenshot to create a New Rule for conditional formatting...
- Carla2404Copper Contributor
THANK YOU! It works. Something so simple. Subodh_Tiwari_sktneer