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

[Darth Chunk]

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​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Jan[/TD]
[TD]2-Jan[/TD]
[TD]3-Jan[/TD]
[TD]4-Jan[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Match 1[/TD]
[TD]Match 2[/TD]
[TD]Match 3[/TD]
[TD]Match 4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Match 5[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET 2 - Conditional formatting applies to this sheet only​

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1-Jan[/TD]
[TD]2-Jan[/TD]
[TD]3-Jan[/TD]
[TD]4-Jan[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Match 1[/TD]
[TD]Match 2[/TD]
[TD]Match 3[/TD]
[TD]Match 4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Match 5[/TD]
[TD]etc...[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

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!

 

[Scott T]

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

=A1>5

you would get the below.

Excel 2010[TABLE="class: grid, width: 200"]
<colgroup><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

Main Data​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=A1>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=A2>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=A3>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C4[/TH]
[TD="align: left"]=A4>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C5[/TH]
[TD="align: left"]=A5>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=A6>5[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C7[/TH]
[TD="align: left"]=A7>5[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]