Hi all @ Ozgrid,
I am trying to implement a conditional format which will apply to a range of cells and it's proving trickier than I had thought.
The objective is as follows;
I have a workbook with multiple sheets, each with a similar array of data. What I want to achieve is a validation check between the sheets, with the aim being that should a cell in one worksheet have data, and the same corresponding cell in the second worksheet NOT have data then the colour of the cell is changed to highlight this and therefore making it easier for the user to see where gaps appear.
E.g.
A1 (sheet1) is BLANK, and A1 (sheet2) is BLANK = No formatting
A1 (sheet1) is NOT BLANK, and A1 (sheet2) is BLANK = Cell A1 in sheet 2 turns Red
A1 (sheet1) is BLANK, and A1 (sheet2) is NOT BLANK = Cell A1 in sheet 2 turns Orange
Problems I have:
1. Cell A1 in sheet 1 may actually refer to Cell C1 in sheet 2 so I need to incorporate a MATCH function into this somehow (in the attached example, cell F5 on Sheet1 refers to cell E4 on Sheet2)
2. The second cell always refers to itself and I am not sure how to work around this (have tried using INDIRECT but can't seem to get it working)
The formula I was using seems not to do the trick.
I have attached a simple spread sheet as an example.
If anything in my description is unclear please let me know and I will clarify.
P.S. The attached example is extracted from another workbook in which I received awesome help with a VBA problem from RoyUK here at ozgrid. The VBA is already in the sheet but is not relevant to this problem, I include it in case someone thinks of using VBA to tackle the problem in case the code clashes.
However I would prefer it if a "simple" formula were the solution.
If anyone can offer help with this I would be most appreciative :thanx: