Need help with Conditional Formatting for Consecutive numbers

Reply #11

omiller315 — Sun, 24 Apr 2011 22:44:35 GMT

You are welcome. This would work for anything with consecutive numbers, but I can understand you wanting to do it without macros. I am working on something for a guy who runs my golf league. I put a button in a field that goes through all scores, finds the most recent 6(even if they miss weeks), drops high and low and then averages out the remaining 4 for the handicap. Like I said, VBA makes working with Excel data simple. He clicks the button and all the work is done for him..

Reply #10

frenchie — Sun, 17 Apr 2011 22:31:58 GMT

Hi omiller,

Thanks for the help but, it has to be a formula for that CF.

Like I said before , I need it also for different data other than draw number, that would actualy have consecutive number too,( I have so many different data to study) it's just that when you look at the data it look nice and clear it's maybe more formula work, but to me it's worth it.I can see better for analizing them.

Thanks anyway, I think this tread is over now.

Serge.

Reply #9

omiller315 — Sun, 10 Apr 2011 17:00:32 GMT

VBA is a powerful tool. What other types of data are you looking into CF for? Also what version of Microsoft Excel are you using

Reply #8

frenchie — Sun, 10 Apr 2011 06:20:49 GMT

Hi omiller315,

Thanks for the reply.

I use the draw results from my state, and when a pair came up I want it to be in green with a border around it etc...for 3 or 4 or 5 consecutive numbers, but sometime you have a draw that have two pairs in it, like for example : 5-23-24-45-46, so those 2 pairs 23-24 and 45-46 should be highlighted in green with each pair a border around it, and the red ( or what ever color ) would be, if 4 numbers are consecutive numbers.like 5-24-25-26-27.

But I don... [ More ]

Reply #7

omiller315 — Sun, 10 Apr 2011 01:15:28 GMT

Frenchie..

I signed up here to reply to your post. I think the only way to make this work is VBA. It is Visual Basic for Microsoft Applications. I went to school for VB.. I can write the code, but no guarantee when I will have time to write it...It shouldn't be too involved. You can add it as a macro and just click play to run through your data... I am comfused by your one post. If you have 4 in a row, you want them to be 2 pairs of green or all 4 of them red? Or do you want the cell to... [ More ]

Reply #6

frenchie — Sun, 03 Apr 2011 06:15:29 GMT

Hi JFN1,

And thank you for your time, but I would like to have the CF directly on the numbers themself without extra or outside formula to make it happen.

I have already the following from someone else, it's pretty good, but still not exactly what I need, very close though, because when I have 2 pairs it should be in green but with this CF I get them in red, or 1 triple and 1 pair I should have the triple in purple and the 1 pair in green but here it's all in yellow !!!

I need the CF wi... [ More ]

Reply #5

JFN1 — Sun, 03 Apr 2011 02:18:51 GMT

if you need something like this let me know

16 22 23 26 35 31 - 22 23 - -

14 33 48 49 50 18 - - 48 49 50

5 8 31 46 50 4 - - - - -

5 9 21 35 38 20 - - - - -

4 27 29 38 45 5 - - - - -

2 15 17 33 35 8 - - - - -

1 4 11 16 45 37 - - - - -

4 22 42 46 53 20 - - - - -

4 8 15 25 47 42 - - - - -

10 12 13 35 56 9 - 12 13 - -

6 18 36 40 49 7 - - - - -

15 16 27 40 52 16 15 16 - - -

8 11 12 31 32 29 - 11 12 31 32

11 20 26 46 53 12

Reply #4

frenchie — Sat, 02 Apr 2011 22:54:39 GMT

Hi JosephusMinimus,

Sorry for the delay, and thank you for your respond, but what I'm looking for is only to highlight the consecutive

numbers ( per draw) serounded by a border and not each number, for my history draw, and I would also use

this formatting for other data.Your formulas don't work for what I need, but thank you for the suggestion.

Best Regards...

Serge.

Reply #3

JosephusMinimus — Sun, 27 Mar 2011 13:22:20 GMT

I was just going over winsumloosesome's past posts and came across this:

https://www.lotterypost.com/thread/228252/1970360

You might find it helpful.

Reply #2

JosephusMinimus — Sun, 27 Mar 2011 12:44:12 GMT

If you're trying to identify when a particular number or combination of numbers happened you might find it easier to use countifs, say, by spreading each number within the history/matrix across the top horizontally to the right of the history beginning with H

Insert =COUNTIFS($A2:$E2,H$1) in H2, then copy and paste the formula all the way across the matrix, then copy and paste those downward until you run out of history.

That will allow you to filter and sort for each number or each combin... [ More ]

Reply #1

JosephusMinimus — Sun, 27 Mar 2011 12:27:27 GMT

I can't suggest a formula but there's a fast way of doing it using search and replace. Highlight A1 through E1, click the menu for search / replace, choose 'Options', Format or choose options from format or cell. Then provide the color choice for each number you want changed to a particular color.

Need help with Conditional Formatting for Consecutive numbers

frenchie — Mon, 21 Mar 2011 03:56:47 GMT

Hi everyone,

I need CF formula that will highlight 2,3,4 or 5 consecutive numbers from 5 numbers combination.

A1:E1 : 5,23,24,36,54. here 23 and 24 should be highlighted in green.

A1:E1 : 5,23,24,25,54. here 23,24 and 25 should be highlighted in red.

A1:E1 : 22,23,24,25,54. here 22,23,24 and 25 should be highlighted bleu.

A1:E1 : 22,23,24,25,26. here 22,23,24,25 and 26 should be highlighted black.

I put the example for each one, for the different colour.

Thank yo... [ More ]