What is the most number of consecutive colors.

[pixel34]

Hi,
I'm sorry for english.
I will tell you that picture.
If you know how to write macros;
There to help.
Thank you very much.
ck521hqwmqnzvvo63.gif

[:) Sixthsense :)]

Instead of picture please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[pixel34]

Hi, Sixthsense

[:) Sixthsense :)]

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

=GetMax(Range,ColorNum)

=GetMax(E2:K2,3)

[pixel34]

Hi, Sixthsense
You worked very hard.
A big thank you for that.

There are also data on this page. And they should be exempted.
To do this, your code. Buttons should work with.
Results area will be written: (B2: B. .....)
Where the area to be checked: (E2: K. ....)
I hope. It was understandable.

summary:
Macro should work with the button.
Work areas should be interfered with.
(B2: B. ...) and (E2: K. ....)

[:) Sixthsense :)]

Sorry I am unable to understand your further requirement which you posted in Post #5

You can adjust the range and use the UDF formula at anywhere in the workbook.

[pixel34]

Example;

Sub Macro()
'..............
'..............
End Sub

[:) Sixthsense :)]

I am ready to convert the UDF to macro but before that I just want to know what is the draw back having it in UDF?

Becuase I believe UDF is more flexible than the Macro code in this case.

[pixel34]

I'm so sorry.
destroy your valuable time.


Whether your macro works as follows.
(B2: last line) and (E2: K last line)

And you're working with a macro button.

Before I forget. Greetings from Anatolia to India.

[jindon]

Try

Please Login or Register  to view this content.

[:) Sixthsense :)]

Try this code…

Please Login or Register  to view this content.

[pixel34]

hi, jindon
great code

I wanted a little more detail would be?

[pixel34]

Hi, Sixthsense

ultra super
Thank you friend.

With the same logic.
Red was the color of column B
Whether black colors column A
possible?

[pixel34]

Excuse me Sixthsense

If the cell is empty, jump in your code should be

Value <> "" Then

[:) Sixthsense :)]

To know the Number of the Font Color just place the cursor on that cell and Press Alt+F11 and Press Ctrl+G and copy paste the below code and hit enter.

?Activecell.Font.ColorIndex

Which will get you the color of the font (In which the cursor is placed)

In the below line of the provided code just replace the 3 (3 for red) to 1 (1 for black) with the number arrived in immediate window.

Please Login or Register  to view this content.

[pixel34]

Excuse me Sixthsense

If the cell is empty, jump in your code should be

Value <> "" Then

did you see this message?

Please compare your code with the code of jindon

[:) Sixthsense :)]

Oops... try try the below code for getting result for both Column-A (For Black Color Text) and Column-B (For Red Color Text)

Please Login or Register  to view this content.

[pixel34]

Dear Sixthsense,

I did the wrong test.
The first summer of the year, even if the macros incorrect.

if you don;
E: K is red all data from
value of "0" is doing.
Yet the value "7" will be.

It also handles empty cells will take.
(Empty cells will be traded.)
(Skip empty cells)


This macro message: 11

Please Login or Register  to view this content.

[:) Sixthsense :)]

Try this...

Please Login or Register  to view this content.

[jindon]

hi, jindon
great code

I wanted a little more detail would be?

What part?

[pixel34]

I am sorry. Dear Sixthsense
But still wrong.

What's wrong? I showed in the example.

[pixel34]

Try

Please Login or Register  to view this content.

Dear Jindon;
Your code is beautiful.
No errors. Doing the correct procedure.
Getting the desired results.

But the code is not fully understood.
Whether you like the following macro style.

Please Login or Register  to view this content.

[:) Sixthsense :)]

@ pixel34,

If jindon's code gets the desired result then please stick with it for fine tuning

Because I don't want both of us (Me and Jindon) to spend much more time for this one unnecessarily. You may be enjoying the varieties but from my point of view your requirement is getting growing for each post of this thread when comparing to your initial thread

[jindon]

What I understand is count the max consecutive font color except the cell with no value and font colorindex = default color.(xlAutomatic)

So it will count consecutive any font color.

Completely different in logic from the other code.

[pixel34]

Dear Sixthsense

It is a good alternative.
Alternative to happen to?

[pixel34]

What I understand is count the max consecutive font color except the cell with no value and font colorindex = default color.(xlAutomatic)

So it will count consecutive any font color.

Completely different in logic from the other code.

Dear Jindon;
I'm unable to use your code in other files.
I'm afraid.
Because the column, row and cell addresses unclear.
I novice user.

[jindon]

The code is specially designed to what you have uploaded.
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Then selection the cells to be calculated and run the code.
However the selection should start from Col.D as least.

If you run the code with the selection includes Col.A-C. it will give you the error.

You know, I don't like to write a code with GUESS....

[pixel34]

I'm really grateful to you.
Dear Jindon.

I think it's code @Sixthsense more valuable for me.
Error correction of hope.

Dear Sixthsense.
You misunderstand me. I felt.
I can not write in English.
These articles are reaching you with google translate.
If there is a mistake I'm sorry.

I you, I'm waiting for answers to 21 numbers of the posts.
If you do not answer, please'll be off topic.
I can not use code Jindon.

[:) Sixthsense :)]

Try this final version of code...

Please Login or Register  to view this content.

Refer the attached file for details. If it gets any wrong results then please highlight those rows with expected result

[pixel34]

This is a great code.
Dear Sixthsense
I'm doing the following experiment.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

[pixel34]

Dear Sixthsense;

21 of the posts I did try the example files in the attachment.
12 lines A12 cell value "0" is not
"1" should be. Because the data in this row are black.

21 of the message with the attached excel
please do the experiment.

[:) Sixthsense :)]

Please attach a file with code and highlight the wrong result with expected answer

[pixel34]

Example is ready.
dear Sixthsense

[:) Sixthsense :)]

Try this revised code...

Please Login or Register  to view this content.

Refer the attached file for details

[pixel34]

Yuuuuuuuuuppiiiiiiiiiiiiiiiiiiiii
You're a great Sixthsense.

Topic is solved.
Please get marked.
I can not.

[:) Sixthsense :)]

You're Welcome

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[pixel34]

I did it.
Thank you again.