Conditionally referencing cells in a contiguous block by color and/or formula Excel 2013

ChillEDog

New Member
Joined
Dec 1, 2016
Messages
3
I've inherited a project. There is an excel workbook for each of 200+ entities unfortunately there were some issues with the originally distributed workbooks. Double-unfortunately, the users have made entries to those workbooks. So we are starting over with fresh workbooks, and need to programatically transfer user-entered data from old (bad) workbook to new workbook. The physical position of the user cells should be exactly the same between old and new.

The user wroksheets have a combination of userinput (yellow) cells and system calculated (not yellow) cells as well as other fields. Since I am new to the project, whatever I do, **I want to make sure I only impact yellow cells**.

I've used indirect referencing formulas in the yellow cells to fetch data from old workbook when it is opened in new workbook via a user form. that part seems to work as expected.

The second step is to reset the indirect reference formulas - this is a one-time operation - leaving only the value, or a blank field. I'm doing this programatically and it works but it is, ummm, not fast.

Although the user worksheets are not uniform with respect to rows, columns, and userinput cells, the all have a grey border around the "user area" (outside this area is hidden system data). And there is a table with metadata for each sheet which contains the top left and bottom right of the grey-bordered area. Which I'm leveraging, but, again, I have to traverse every cell, see if it is yellow, see if it has a formula, if so does the formula contain a certain range name - if so, clear the formula (cell.value = cell.value).

What I'm getting at is, I know the range I'm interested in, but I only want to act on certain cells in that range (per above... yellow, has formula, etc.). I might be able to settle for just yellow.

Any suggestions ? Thank you !
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Just to follow up on my question, what I have in mind, not necessarily knowing whatsoever what I'm talking about, I can define a primary range using the existing metadata. That would be my starting point. Within that range, I'm interested in a subset of cells (the yellow ones) which are not necessarily contigous. Is there such a thing as subranges ? Or is this wishful thinking ?

Of course any other suggestions would be of interest too.

Thanks !
 
Upvote 0

Forum statistics

Threads
1,213,483
Messages
6,113,919
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top