PDA

View Full Version : Conditional Formatting in VBA



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

Aussiebear
04-04-2012, 04:43 PM
Checking of the data occurs when? Once the data is entered or often during all the processes?

phrankndonna
04-05-2012, 01:10 PM
It would be best if I could make the conditional formatting work within the worksheet, because then each cell would be checked immediately when a value is entered. But it doesn't appear that this is possible, because users often insert and delete rows and columns, which changes the cell references. So, as often as possible would be best to check the data. Thanks!

Frank

Aussiebear
04-05-2012, 04:54 PM
Are the cut & paste, insert & delete actions going to be the same at the same stages? If not then I believe it is going to be very difficult to check, unless the layouts are the same. If they were to be the same then perhaps a series of checks can be designed to be applied at specific stages.

phrankndonna
04-06-2012, 05:26 AM
No, there will be no set stages where cut/delete/insert/paste will take place; that will depend on the project and the endpoint.

There must be a way to point to a specific cell in another sheet no matter what is done to that other sheet. That's all that is needed here.

Thanks for looking at this.

Frank

Aussiebear
04-06-2012, 06:35 AM
Once the data is entered and checked, it seems very difficult then to initialise any further checking steps from what you have indicated.

phrankndonna
04-10-2012, 04:38 AM
Can anyone tell me if there is a way to LOCK DOWN that 'Applies to' field in the conditional formatting rules manager (Excel 2010) using VBA or otherwise?

I've played around a lot with this, and i've looked all over the internet, and I'm just not sure I will ever be able to get this to do what I need with the current capabilities of Excel. It is indeed frustrating, because this conditional formatting formula and process would significantly alter and improve the way we do this task here at work.

The issue isn't with the actual Conditional Formatting formula, but rather with the 'Applies to' area. The conditional formatting formula is indeed looking at a second sheet, but the applies to area is looking at the current sheet. If any rows or columns in the other sheet being referred to in the formula get inserted or deleted, then the 'Applies to' field changes. Oftentimes a subsequent rule with the new 'Applies to' range gets added.

I do hope there is a way to lock this down, but I'm not feeling overly optimistic at the moment. Thanks.