Conditional Formatting: Based on comparison of cells between worksheets, if cell is blank

Darth Chunk

New Member
Joined
May 12, 2008
Messages
4
Hi all @ Mr Exel!

I have a conditional formatting problem I can't quite get my head around and I'm hoping someone can help me out.

First let me just say that I have been unable to find anything in the existing forum topics that resolves this, but I have been able to make some progress using what I've read elsewhere. Hopefully someone can help me past the finishing line with this one!

What I am trying to achieve:


  1. Compare cells in two arrays, each in a different worksheet. Depending on the results of the comparison, conditionally format the cell in the 2nd array only.
  2. If the cell in sheet 1 is not blank & the cell in sheet 2 is blank, then conditional format "type 1"
  3. If the cell in sheet 1 is blank & the cell in sheet 2 is not blank apply conditional format "type 2"
  4. If the cells in both sheets are blank, then no conditional formatting applies


I have been able to successfully achieved the formatting I want, but so far have only managed to make it apply to a single cell. I have stacked to rules as follows;

1. =AND(NOT(ISBLANK(Sheet1!$F$5)),COUNTBLANK($F$5)) (Set format 1) - Stop if true
2. =AND(COUNTBLANK(Sheet1!$F$5),NOT(ISBLANK($F$5))) (Set format 2) - Stop if true

This now works as needed, in that if there is data in Cell F5 of sheet one, but not in F5 of sheet 2 then then format 1 is set. Vice versa, if there is data in cell F5 of sheet2 but NOT in F5 of sheet1 then format 2 is set. Is the cells in both sheets are blank, then no format is set.

What I have not been able to do is apply this formula to the entire range of cells in sheet 2. Effectively, the conditional format needs to check the active cell it is referring to and compare it to the corresponding cell in sheet 1 before deciding which conditional format applies. I was thinking that a formula using INDEX & MATCH may be required but I cannot see how to get the conditional formatting to check against dynamic cells (as the formulas I list above use an absolute reference to the cell in question).

A very simplified rendition of the spreadsheet is below;


SHEET 1​

1-Jan2-Jan3-Jan4-Jan
AMatch 1Match 2Match 3Match 4
BMatch 5etc...
C
D

<tbody>
</tbody>


SHEET 2 - Conditional formatting applies to this sheet only​

1-Jan2-Jan3-Jan4-Jan
AMatch 1Match 2Match 3Match 4
BMatch 5etc...
C
D

<tbody>
</tbody>

Why am I looking for blanks instead of matching values you might ask? Simply because the data that is entered into sheet1 will never match that of sheet2, but the sheet user needs to know if the corresponding cells in either sheet contain or do not contain data.

Can anyone please help me with this? I am slowly going bald from all the hair pulling :confused: :oops:

A final note: I would prefer if at all possible not to rely upon VBA to achieve this, as the spreadsheet in question already has a lot of VBA coding and it may clash. If this can be achieved using formulas with conditional format that would be the best result. If not, then a VBA solution is also welcomed, and I may just have to see how it affect the current code.


Thanks for looking! :)
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Scott T

Well-known Member
Joined
Dec 14, 2016
Messages
2,616
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
To conditional format multiple cells select the cells and then do conditional formatting. The formula will be applied as if it was being copied (cell references will change unless they are locked) for example if you select A1:A7 and in conditional formatting use
Code:
=A1>5
you would get the below.

Excel 2010
ABC
11FALSE
22FALSE
33FALSE
44FALSE
55FALSE
66TRUE
77TRUE

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
Main Data

Worksheet Formulas
CellFormula
C1=A1>5
C2=A2>5
C3=A3>5
C4=A4>5
C5=A5>5
C6=A6>5
C7=A7>5

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,126,956
Messages
5,621,809
Members
415,857
Latest member
braunReivn

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