Formatting based on a secondary workbook/worksheet

[veblen]

I am need of a solution to a problem dealing with highlighting percentage values based on significance tests.

I was thinking the best way would be to check the percentage worksheet(s) against a Boolean worksheet(s) identical in structure to the percentage worksheet(s), but with 1's denoting what cells should be highlighted.

The vba script needed is one comparing workbook 1 (percentage worksheets) to workbook 2 (boolean of test values) (i.e., highlight or not) and then highlighting the cells in the workbook 1 where 1's are found in the workbook. 2.

Also, all of the worksheets (i.e., percentage worksheets and boolean worksheets) could reside in the same workbook if that allows for an easier/cleaner solution.

Any input would be highly appreciated.

Please let me know if any additional info is required.

Hopefully this receives the first post "special" treatment.

btw: I am posting to this forum since it's provided threads to help resolve problems and/or find solutions in the past where others could not.

 

[bensonsearch]

I am need of a solution to a problem dealing with highlighting percentage values based on significance tests.

I was thinking the best way would be to check the percentage worksheet(s) against a Boolean worksheet(s) identical in structure to the percentage worksheet(s), but with 1's denoting what cells should be highlighted.

The vba script needed is one comparing workbook 1 (percentage worksheets) to workbook 2 (boolean of test values) (i.e., highlight or not) and then highlighting the cells in the workbook 1 where 1's are found in the workbook. 2.

Also, all of the worksheets (i.e., percentage worksheets and boolean worksheets) could reside in the same workbook if that allows for an easier/cleaner solution.

Any input would be highly appreciated.

Please let me know if any additional info is required.

Hopefully this receives the first post "special" treatment.

btw: I am posting to this forum since it's provided threads to help resolve problems and/or find solutions in the past where others could not.

Hi And welcome!

I to love this place, good answers and hopefully i can start helping

I would need more info because im thinking the "test" to highlight can be coded into the VBA. This would allow you to half the space and not have to copy data and edit with 1s etc

Lets see if i understand your logic thou.

dim row as integer 'row is the row of sheets starting at 1 and moving down

row = 1

do until row = (whatever last row is)

If thisworkbook.sheets("sheet2").range("A"&row).value = 1 then
cell in sheet 1 at range A & row format = blah blah
Else
nothing
End If

row = row + 1

loop

????? is this what you want done?

 

[veblen]

Thanks for the reply and welcome, Bensonsearch!

Regarding doing the test highlighting in VBA: The stats come from SAS procedures so the options that I have found are exporting the highlighting with the file or following a path similar to the one outlined.

I am not a VBA programmer, but I would think to do the test highlighting in VBA that both the statistical results and the definition for highlighting those conditions (i.e., what determines if it is 1 or 0) would be required. If that's the case then I see exporting a boolean table as the best solution.

Regarding your feedback: The looping shows that I must have missed the point that there is a cell-to-cell relationship between the two worksheets (i.e., the percentage worksheets and the boolean worksheets). They would be identical in structure, just that the content would be different.

In my naive mind (and since I've done it with other languages) I was under the impression that I could do a cell-to-cell check of the two sets of identical worksheets. The percentage report would have the cells updated based on the boolean worksheet's value.

Am I too naive?

I will think through the looping logic to see if there would be any gaps, but I would think that some form of table-to-table or worksheet-to-worksheet matching would be cleaner.

Please let me know if additional information is required.

 

[bensonsearch]

Thanks for the reply and welcome, Bensonsearch!

Regarding doing the test highlighting in VBA: The stats come from SAS procedures so the options that I have found are exporting the highlighting with the file or following a path similar to the one outlined.

I am not a VBA programmer, but I would think to do the test highlighting in VBA that both the statistical results and the definition for highlighting those conditions (i.e., what determines if it is 1 or 0) would be required. If that's the case then I see exporting a boolean table as the best solution.

Regarding your feedback: The looping shows that I must have missed the point that there is a cell-to-cell relationship between the two worksheets (i.e., the percentage worksheets and the boolean worksheets). They would be identical in structure, just that the content would be different.

In my naive mind (and since I've done it with other languages) I was under the impression that I could do a cell-to-cell check of the two sets of identical worksheets. The percentage report would have the cells updated based on the boolean worksheet's value.

Am I too naive?

I will think through the looping logic to see if there would be any gaps, but I would think that some form of table-to-table or worksheet-to-worksheet matching would be cleaner.

Please let me know if additional information is required.

Hi,

im trying to understand the 2 workbooks. the looping has kept the relationship (A2 on booleen sheet tells A2 on other sheet what to do)

Purhaps a test set of data for me to actually look at? the coding should not be hard. I wouldnt even use booleen (True or False) as you are using 1 and 0

I beleve the last post would do it but again i may just be reading things wrong

let me know of some example workbooks and ill play around

 

[veblen]

THANKS for the input, Bensonsearch!!!

Yes, if the looping holds than it should work. So in addition to the code you have there is just a color formatting change for Workbook 1?

So what would you use instead of a boolean table?

I have weekly data of percentages which I must highlight at certain significance levels. I did not think that just the test results could be exported because I will not know the significance levels to highlight, I will just have the results. So I set that info via a boolean table and then I know what "cells" to be highlighted.

Since I am looking to finish this I was even looking at doing the testing within VBA but as of yet had time to do any research.

But it looks as though the looping might work well, unless your ideas on not employing a boolean table sound better.

I am all ears...eyes...regarding any assistance.

It is highly appreciated to say the very least.

Anyway, I will put together a couple of dummy workbooks and attach them as soon as I find out how.

Until I can post the attachments.

The structure of Workbook 1 worksheets are identical to Workbook 2 worksheets.

Workbook 1:
The structure of the worksheets are all identical.
Weekly or monthly data with the time period being the columns.
Percentage Data in columns from A to currently U, but will be expanding each week/month.

Workbook 2:
The structure of the worksheets are all identical.
Weekly or monthly data with the time period being the columns.
Boolean denoting highlighting or not, in columns from A to currently U, but will be expanding each week/month.

THANKS!!!

 

[veblen]

So Bensonsearch, what's the process of providing samples?

 

[bensonsearch]

So Bensonsearch, what's the process of providing samples?

to be honest not to sure , maybe insert hyerlink to somewhere i can access.

with regards to vba testing. if its a matter of a percentage range then.

dim asd as integer

asd = thisworkbook.sheets("Sheet 1").range("A"&row).value 'this would be where the percentage is so may not be A

if asd >1 and <10 then
Low
elseif asd >10 and <20 then
Med

etcetc
end if

 

[veblen]

Unfortunately, it's just not stat ranges so the boolean table design appears to be the best.

I'm thinking that what you put in your first post will work, but I have yet had time to try it.

I'm just wondering if it's easier to do it by having all of the worksheets in a single workbook, making it something like worksheet 1 is compared to worksheet 11, worksheet 2 is compared to worksheet 12, etc., etc..

or is using the two workbook approach better?

or does it matter.

Not sure if I can get to some dummy workbooks tonight, but if not I plan to give the code mentioned a try.

and of course if you have any input on using one or two workbooks.

------

A sample set of workbooks could contain:
workbook 1
2 worksheets each 8 rows by 10 columns populated with random numbers.

workbook 2:
2 worksheets each 8 rows by 10 columns populated with a random distribution of 1's and 0's. One or two 1's per column is fine.

workbook 1 worksheet 1 compared to workbook 2 worksheet 1
workbook 1 worksheet 2 compared to workbook 2 worksheet 1

The check would begin with B2 due to a header row and a label column

(In reality the worksheets will have different number sets but for testing they can be the same.)

THANKS AGAIN Bensonsearch!!! Not only is your code input going to help but just being able to "describe" and discuss the task has helped me tremendously.

 

[bensonsearch]

Unfortunately, it's just not stat ranges so the boolean table design appears to be the best.

I'm thinking that what you put in your first post will work, but I have yet had time to try it.

I'm just wondering if it's easier to do it by having all of the worksheets in a single workbook, making it something like worksheet 1 is compared to worksheet 11, worksheet 2 is compared to worksheet 12, etc., etc..

or is using the two workbook approach better?

or does it matter.

Not sure if I can get to some dummy workbooks tonight, but if not I plan to give the code mentioned a try.

and of course if you have any input on using one or two workbooks.

------

A sample set of workbooks could contain:
workbook 1
2 worksheets each 8 rows by 10 columns populated with random numbers.

workbook 2:
2 worksheets each 8 rows by 10 columns populated with a random distribution of 1's and 0's. One or two 1's per column is fine.

workbook 1 worksheet 1 compared to workbook 2 worksheet 1
workbook 1 worksheet 2 compared to workbook 2 worksheet 1

The check would begin with B2 due to a header row and a label column

(In reality the worksheets will have different number sets but for testing they can be the same.)

THANKS AGAIN Bensonsearch!!! Not only is your code input going to help but just being able to "describe" and discuss the task has helped me tremendously.

your more than welcome. I just hope i can help in some way

with my code though, it is comparing data based on the fact they are in the same workbook (can be modified)

I was thinking Sheet1 can be compared to sheet 2 and get your answer.

Sometimes just writing down your idea and try to get others to understand the logic makes you realise what you need to do (after all coding is nothing but logic)

 

[veblen]

"Sometimes just writing down your idea and try to get others to understand the logic makes you realise what you need to do (after all coding is nothing but logic)"

- without question.

I will let you know how it works.

THANKS!!!