Conditional formatting with two formulas (If and)

Peter Davison

Board Regular
Joined
Jun 4, 2020
Messages
223
Office Version
  1. 365
Platform
  1. Windows
Hi,

I have a spreadsheet Sheet 1 (My main sheet)
It pulls formulas from 4 other different sheets Lets say Sheets 2 to 5) based on a validation list in Sheet1 cell A1 (The list is held in Sheet1 NN1 to NN4)
When sheet 2 is chosen on the list it compares cells in Sheet 1 to Sheet 2 and has a conditional format set to show differences. This is fine and works ok.
What I want to do is when I choose Sheet 3 from the validation list my conditional formats don't work because they relate to Sheet 2
Is there a way I can use "if" with "and" in the conditional format (Use a formula to determine which cells to format) so whichever sheet I was choosing on my list the conditional formats looked at that sheet

I was thinking the formula would be something like this
=if(and(A1 = NN1,Sheet1!C6<>Sheet3!C6))
this is where I then set the colour the cell would be

I just can't think how to do this?
Any help would be appreciated.
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,907
Office Version
  1. 2016
Platform
  1. Windows
Hi Peter Davison,

You could use an IF statement but Conditional Formatting is applied where the condition resolves to TRUE and isn't when it resolves to FALSE, so you could just use:
Excel Formula:
=AND(A1=NN1,Sheet1!C6<>Sheet3!C6)
 

Forum statistics

Threads
1,140,928
Messages
5,703,220
Members
421,283
Latest member
MacroBegin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top