Columns of data ... need a macro that can find differences of 0.06 & Fill Color them

**rocket1406** · 07-16-2007, 02:10 PM

Hey guys.

My eyes spin like crazy when I try to do this manually. I need your help again!

I have columns of black data that I manually go through and find regions that have cells with values that are 0.06 or more different.

I then turn these regions into blue colored regions.

HOW YOU GUYS CAN HELP:

Is there any way you can design a macro that goes through the spreadsheet and FILL COLOR YELLOWs a cell who is 0.06 or more less then the next cell down?

This would be so much easier on my eyes, since I usually have 80 columns and 1500 rows of data.

The blue coloring I still have to do manually because my human instinct is sometimes important there.

I would be using the FILL COLOR YELLOW cells as quick markers for my regions of interest.

I've attached a sample worksheet showing what I'd like. (please note the 3 sheets in the sample)

Thanks so much once again!

**Andrew Hicks** · 07-16-2007, 02:47 PM

Please read forum rules. Code should always be wrapped

Hi

I don't know if this will help you. It is a slight adaptation of a more general purpose routine to colour cells from a range of colours. I have left the colour codes in so you can adapt it for other colours if you need to.

You need to identify the row (r) and column(c) of the cell you wish to colour in then call this subroutine.

Please Login or Register  to view this content.

Good luck. I hope it works for you.

**rocket1406** · 07-16-2007, 03:01 PM

Hi thanks for your help. But I can't get Excel to recognize the macro.

Is it possible for you to edit your macro to make it more "newbie" friendly for me?

When I make the Module (Alt+F11) it doesn't show up in Tools Macros...

Also how do I specialize it to the sheet of interest when it does work?

And what substitutions am I supposed to make? Where it says colorindex=yellow I changed it to colorindex = 44? anything else? Do I have to change r and c to numbers? I'd like it to be all rows and columns

Also, is the 0.06 factored into this macro? I see "27" but not greater than or equal to 0.06 for the row differences in the same column.

Column B
1.12 - yellow (next value is 0.06 or more greater)
1.18 - nothing (next value is not 0.06 or more greater)
0.70 - nothing
0.70 - yellow (next value is 0.06 or more greater ... in this case 0.07)
0.77 - nothing
0.80 - nothing

**rocket1406** · 07-16-2007, 10:27 PM

Anybody? :-p

**davesexcel** · 07-16-2007, 10:52 PM

Here's a little loop that could be what you are looking for, right now it just works for column E but could be advanced to go through all columns

Please Login or Register  to view this content.

hit alt f11
goto insert
module
copy and paste the code
go to a spreadsheet and hit
alt f8
select the macro to run

**rocket1406** · 07-17-2007, 09:38 AM

Thanks to both of you I will try it when I get home from work!

**rocket1406** · 07-17-2007, 11:07 PM

Hey davesexcel,

It's ALMOST there.

It almost worked, but it did 0.06 or more less "after". I need it to be "before".

Like instead of:

1.4
0.76 - yellow

(0.76 should remain black)

I need it to be like:

0.67 - yellow
0.94

I need yellow cells right before a rise, instead of right after a fall basically. I think we just need to change a minor thing but I don't understand code hehe.

**davesexcel** · 07-17-2007, 11:53 PM

check the edited code,
it is untested

**rocket1406** · 07-18-2007, 12:50 AM

perfect. now is there any way to get this for Column B, and then Column B+3, or E, Column B+6, or H

basically

B, E, H, ... until like column BZ or w/e

**davesexcel** · 07-18-2007, 06:43 AM

Try This

Please Login or Register  to view this content.

**rocket1406** · 07-18-2007, 08:33 AM

hey dave,

it worked for the column B

and then for column E it didn't do anything

"Run-time error 13: Type mismatch"

**davesexcel** · 07-18-2007, 11:06 AM

Originally Posted by rocket1406

hey dave,

it worked for the column B

and then for column E it didn't do anything

"Run-time error 13: Type mismatch"

maybe change this

Please Login or Register  to view this content.

**rocket1406** · 07-18-2007, 11:45 AM

Hey thanks. It's really ALMOST there.

Now it doesn't give an error, and does what I want.

The last problem:

It's stopping at Column K for some reason.

(I added some more columns with the same layout to see what would happen)

How do I make it act on more columns

**rocket1406** · 07-18-2007, 09:11 PM

Guys, anyone know how to simply make the macro work on the whole sheet ... I don't think THAT can be a tough issue, or is it?

**rocket1406** · 07-19-2007, 08:07 AM

LOL dave :-p

So what do I do with the c? Which c? I'm a noob.

**rocket1406** · 07-19-2007, 08:17 AM

LOL THANK YOU DAVE!

I can't believe all I had to do was change 1 to 3 to 1 to __.

I'm such a noob at this.

THANKS!!!!!!!

I love you guys. I love this board!

**rocket1406** · 08-22-2007, 10:53 AM

Hi this is open to dave or anyone out there who can help:

The macro I use (modified davesexcel one) is:

Please Login or Register  to view this content.

What I would like to know is if it can Red Fill any number that is 0.15 greater than the Yellow Fill before it?

For example:

0.64 - yellow fill (macro does this already)
0.71
0.74
0.77
0.8 - red fill (0.16 greater)
0.6 - yellow fill (macro does this already)
0.66
0.6 - yellow fill (macro does this already)
0.69
0.72
0.76 - red fill (0.16 greater)
0.8 - red fill (0.20 greater)
0.62

Since the 0.8 is 0.16 greater than it's yellow 0.64, if it could be filled red.
Since the 0.66 is NOT 0.16 greater than it's yellow 0.66, it's NOT filled red.
Since the 0.76 is 0.16 greater than it's yellow 0.6, if it could be filled red.
Since the 0.8 is 0.20 greater than it's yellow 0.6, if it could be filled red.

Thanks so much. If there is confusion do you guys need me to show you on the original sample of this thread? Let me know. Thanks!

**davesexcel** · 08-22-2007, 11:22 AM

Hi rocket1406,
Could you zip a copy of the workbook and attach it to your next thread??

**rocket1406** · 08-22-2007, 11:23 AM

sure i'll do it right now... one sec

**rocket1406** · 08-22-2007, 11:32 AM

Hi dave, I've attached a sample.

Instead of 0.15, the difference I want is 0.10 if that is okay. (Because you'll see 0.89 - 0.73 = 0.13, but that is still of interest to me).

**davesexcel** · 08-22-2007, 12:36 PM

Originally Posted by rocket1406

Hi dave, I've attached a sample.

Instead of 0.15, the difference I want is 0.10 if that is okay. (Because you'll see 0.89 - 0.73 = 0.13, but that is still of interest to me).

Hi try this out

Please Login or Register  to view this content.

**rocket1406** · 08-22-2007, 06:17 PM

hey man,

works great. thanks so much!

**rocket1406** · 08-25-2007, 12:25 PM

Hi Dave,

I'm sorry to bother you again... but just one thing I noticed ... the reds only work if it's right after a yellow/direct jump

Some of my rises are more gradual and the macro doesn't work for them (the red fill) Is there any way to make it red fill if the rise is more gradual?

I've attached a sample to show what I mean.

**davesexcel** · 08-25-2007, 12:57 PM

What I would like to know is if it can Red Fill any number that is 0.15 greater than the Yellow Fill before it?

That's what you asked for,
What is the purpose of this ?
maybe here is another way like conditional formatting

**rocket1406** · 08-25-2007, 01:13 PM

Hi Dave,

Yeah I'm sorry for the wording.

What I was trying to say is if there is a YellowFill#1, #1, #2, #3, #4, YellowFill#2, and #4 is 0.1 (or 0.15) greater than YellowFill#1, if it could be RedFilled.

that's what I meant by "the yellow fill right before it"

These are visual markers for my going through tens of thousands of numbers.

The red fill color macro you created works GREAT but some of my peaks are missed because the increases are gradual.

Thanks for your continued help, Dave. You have already saved me tons of hours AND potential errors. This was just like a final request if it was possible. If this is too hard to do, then I guess I'll have to do it less efficiently.

**rocket1406** · 08-25-2007, 01:18 PM

the purpose of this is to find just the "mountains" in huge data files.

the living cells calcium levels increase and then decrease.

the problem is sometimes they don't decrease back to the starting level ... which is why the red fill becomes important.

the red fill can help me determine if the 2ndary increase i'm seeing is a new "mountain" or just a fluctuation of the same "mountain"

that's why I don't know if 0.1 or 0.15 or 0.?? works best.

however I can't miss gradual "mountains" or else the scientific accuracy of the data will be tarnished ... and that's why I come to you for this final request.

**rocket1406** · 08-25-2007, 01:44 PM

Dave, I just had an idea. I don't know if this will make it easier, but it's a slightly different approach.

Tell the macro to look between every region of yellow, and find the highest number between that region. If that highest number is 0.15 greater than the start yellow of the region, Red Fill it. If it is not, do nothing.

Example:
0.6 Yellow #1
1.2 No Action
1.5 Red Fill (highest)
0.6
0.6 Yellow #2
0.7 No Action (highest, but less than 0.15)
0.6 Yellow #3
0.7

**rocket1406** · 08-26-2007, 07:29 PM

dave bro is it possible?

**rocket1406** · 08-27-2007, 03:16 AM

Thanks Dave and to your recommendation as well!

Solution:

helped by davesexcel and Tim O.

Please Login or Register  to view this content.

Columns of data ... need a macro that can find differences of 0.06 & Fill Color them

LinkBack

Thread Tools

Rate This Thread

Display

Columns of data ... need a macro that can find differences of 0.06 & Fill Color them

Yellow cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions