phrankndonna
04-03-2012, 07:52 PM
Hi, I
Hi. I'm using Excel 2010. And I've tried to set up conditional formatting for all cells in one worksheet based on the values in another worksheet (and vice versa), and it works, but there is a glitch when sections of either worksheet are cut/pasted and/or deleted. Doing that alters the formula in the conditional formatting, and I haven't found any way to keep the formula static.
Background:
I've got two sheets where the same data is transcribed from paper and entered on both sheets. One person enters it on Sheet1, and a second person enters it on Sheet2. This is done to ensure accuracy and integrity of that data conversion to electronic format. Each workbook actually has between 6 and 12 sheets, or rather between 3 and 6 compares (i.e., Sheets 1 and 2 compare to each other, Sheets 3 and 4, Sheets 5 and 6, etc.)
The conditional formatting formula I have in Sheet1 is: =NOT(EXACT(A1,'Sheet2'!A1)). And, I have the formula applying to =$A:$IV. The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)). And if anything is not the same on the opposite sheet, the cell turns yellow. I've also tried it with static cell references (i.e., $A$1) with the same results.
This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconcilliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area, and even ADDS additional conditional formatting rules automatically.
Is there a way using VBA to reset the formula when the workbook is saved? Keeping in mind that Sheet 1 compares to Sheet2 (and vice versa), Sheet 3 to Sheet 4 (and vv), etc. If the formula was the same on each sheet, it would be very simple, but it changes based on the name of the sheet to the right or left of it.
Thoughts? And thanks!
Frank
Hi. I'm using Excel 2010. And I've tried to set up conditional formatting for all cells in one worksheet based on the values in another worksheet (and vice versa), and it works, but there is a glitch when sections of either worksheet are cut/pasted and/or deleted. Doing that alters the formula in the conditional formatting, and I haven't found any way to keep the formula static.
Background:
I've got two sheets where the same data is transcribed from paper and entered on both sheets. One person enters it on Sheet1, and a second person enters it on Sheet2. This is done to ensure accuracy and integrity of that data conversion to electronic format. Each workbook actually has between 6 and 12 sheets, or rather between 3 and 6 compares (i.e., Sheets 1 and 2 compare to each other, Sheets 3 and 4, Sheets 5 and 6, etc.)
The conditional formatting formula I have in Sheet1 is: =NOT(EXACT(A1,'Sheet2'!A1)). And, I have the formula applying to =$A:$IV. The formula in Sheet 2 is similar, except it is pointing at Sheet1:=NOT(EXACT(A1,'Sheet1'!A1)). And if anything is not the same on the opposite sheet, the cell turns yellow. I've also tried it with static cell references (i.e., $A$1) with the same results.
This works beautifully as long as only data is entered. If by chance, however, columns or rows are deleted (which they oftentimes need to be during the reconcilliation process), then the formulas and/or the regions they apply to are thrown completely off. Additionally, if/when data is copied from one location to another, it changes the 'Applies to' area, and even ADDS additional conditional formatting rules automatically.
Is there a way using VBA to reset the formula when the workbook is saved? Keeping in mind that Sheet 1 compares to Sheet2 (and vice versa), Sheet 3 to Sheet 4 (and vv), etc. If the formula was the same on each sheet, it would be very simple, but it changes based on the name of the sheet to the right or left of it.
Thoughts? And thanks!
Frank