PDA

View Full Version : Seek help to highlight number.



apple
07-18-2007, 12:49 AM
Hi,

Please do show me the way to highlight 5 number in front and 5 number at the back. For example the number i want is 12345-ABC-67890.I want to highlight all the number that is 5 number in front, middle got ABC and at the back is 5 number.

Besides that i want highlight the sequence number which is start from 1 to 1.1, 1.1.1, 1.1.2 and so on. After that start loop 2 to 2.2.1,2.2.2, 2.2.3 and so on.

Below is the example:

1.1.0 12345-ABC-647891
1.1.2 67891-DEG-112345
1.1.3 78910-HIJ-123467

2.1.0 65478-KLM-645789
2.2.1 45678-OPQ-489453


I would like to highlight the number follow the sequence for number 1.1.0 to the end 1.1.3 that have 5 number in front, middle ABC and 5 number at the back. After that start loop 2.1.0 until end of 2.2.1
I am new to VBA. Can anyone help me with example? Thanks. Hope to hear any friend to help me soon.

Regards,
Apple

JimmyTheHand
07-18-2007, 01:51 AM
Hi,

So you want something like this?

1.1.0 12345-ABC-647891 --> 1.1.0 12345-ABC-647891

If not, then can you show me an example?

2nd question: You want this for all rows? Where are these rows, anyway?

Jimmy

apple
07-18-2007, 07:58 AM
Hi Jimmy,

Yes, you are right. I want 1.1.0 12345-ABC-647891, all the rows. This rows are from excel sheet. Hope to hear from you soon.

apple
07-18-2007, 08:13 PM
Hi Jimmy,

Any update for my request. Thanks

JimmyTheHand
07-18-2007, 11:51 PM
Hi,

try the macro below.

Sub HighLight()
Dim Rng As Range, c As Range
Dim L As Long, S As String
Set Rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each c In Rng.Cells
If c <> "" Then
S = c.Value
With c.Characters(1, InStr(S, "-") - 1).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
With c.Characters(InStrRev(S, "-") + 2).Font
.FontStyle = "Bold"
.ColorIndex = 3
End With
End If
Next
End Sub Please note the red bold line.
This line defines the cell range that contains these serial (?) numbers. Since I don't know the layout of your excel sheet, I assumed that the numbers are in column A, starting with A1, and there is no other data in column A, only these numbers.

If you have them elsewhere, e.g. on a fixed cell range, or want to run the macro on a range previously selected by user, etc, we can arrange that as well, but I need to know, where to search for these numbers.

Jimmy

apple
07-19-2007, 03:02 AM
Hi Jimmy,

Thanks for your respond. But i need the number for example
12345-ABC-67891. That means 5 number in front and at the back and the middle have ABC alphabet. If the looping see that number then it will highlight. Can you advise me for this problem? Thanks

JimmyTheHand
07-19-2007, 04:00 AM
Please post a sample workbook.
Put the numbers into column A, and put the desired result into column B.

JImmy

apple
07-19-2007, 06:22 AM
Hi Jimmy,

I already attach for you the example. Please see as below:

1.1 12345-ABC-67891 *Must have type A
- i want to auto highlight when press button highlight then highlight 5 number in front, ABC alphabet in middle and 5 number at the back.
Type A. Then highlight OR
1.1.3 12356-ABC-52345 number in front and middle ABC also type A.

Please refer the attachment in yellow colour continue loop until end of 1.

Then continue find in green colour
*Must have type B
1.1.4 12346-DEF
- 5 number in front and middle must have DEF, type B OR

1.1.9 45689-DEF-45612
-I also need 5 number in front, middle 3 alphabet DEF and 5 number at the back. Type B

Jimmy, hope to see your reply soon. Thanks

JimmyTheHand
07-19-2007, 07:50 AM
I'm sorry, I still don't get it. I spent 40 minutes on trying to understand what you want to highlight, and what do you mean by "highlight", in the first place, but I had no success.

Try to explain yourself better, otherwise I can do nothing for you. I see that there are 3 columns here, so why don't you put the numbers into columns D, E and F, highlighted as you want them, so that I can see your meaning.

Jimmy

apple
07-19-2007, 08:18 AM
Hi Jimmy

Basically the attachment that i send to is the answer that i want to do in coding. There are 2 type highlight that i want to do.

1. Firstly the yellow colour is the one i want to highlight 5 number in front, middle ABC and 5 number at the back, plus the criteria type is A

2. Secondly the green colour is the one i want highlight 5 number in front middle DEF and at the back 5 number plus the criteria type is B

The attachment is the example answer that i want to do

apple
07-19-2007, 08:20 AM
Hi Jimmy,

Do ask me if you still not understand. Thanks in advance

mdmackillop
07-19-2007, 08:28 AM
Hi Apple,
Please repost your example showing the desired result.

apple
07-19-2007, 08:40 AM
Hi,

The attachment is the answer that i want. Before that, the sheet is without highlight. After press highlight button, this is the answer that i desire. Thanks

apple
07-19-2007, 08:56 AM
Hi,

To avoid confuse, i have modify the attachment to the row that i want.
Thanks

mdmackillop
07-19-2007, 10:41 AM
From your example in Post 14, you are colouring Type A yellow and Type B green. I can see no connection to your criteria in Posts 8 and 10. Add some comments to each row in your example explaining why it is coloured as it is.

JimmyTheHand
07-19-2007, 11:43 AM
I think I got it now! :yes

So, you want to highlight in yellow all numbers for which these conditions are true
characters #1-5 are numbers
characters #7-9 = "ABC"
Type is "A"And, you want to highlight in green all numbers for which these conditions are true
characters #1-5 are numbers
characters #7-9 = "DEF"
Type is "B"If i got it right, then see the macros below. They do basically the same thing, except the 1st one is more robust, and works always (I believe), while the 2nd one is easier to understand, but might fail in some cases.

The second macro replaces all digits by a "~" character, and compares the result to "~~~~~-ABC" and "~~~~~-DEF". If the original number contains "~" character (e.g. 1.1.0 12~45-ABC-647891)then the macro might give false positives.


Sub Highlight()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, SType As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
SType = 0
S = Left(c.Offset(, 1), 5)
If CStr(Val(S)) = S Then SType = SType Or 1
S = c.Offset(, 1)
If InStr(S, "-") = 6 Then SType = SType Or 2
If Mid(S, 7, 3) = "ABC" Then SType = SType Or 4
If Mid(S, 7, 3) = "DEF" Then SType = SType Or 8
If c.Offset(, 2) = "A" Then SType = SType Or 16
If c.Offset(, 2) = "B" Then SType = SType Or 32

If SType = 23 Then c.Resize(, 3).Interior.ColorIndex = 6
If SType = 43 Then c.Resize(, 3).Interior.ColorIndex = 35
Next

End Sub
Sub Highlight2()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, i As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
S = c.Offset(, 1)
For i = 0 To 9
S = Replace(S, CStr(i), "~")
Next
If Left(S, 9) = "~~~~~-ABC" And c.Offset(, 2) = "A" Then c.Resize(, 3).Interior.ColorIndex = 6
If Left(S, 9) = "~~~~~-DEF" And c.Offset(, 2) = "B" Then c.Resize(, 3).Interior.ColorIndex = 35
Next
End Sub


Hope this helps.

Jimmy

P.S.
Thanks, MD, for stopping by and helping to clear up the confusion. :thumb

apple
07-19-2007, 05:29 PM
Hi Jimmy,

Thanks for your reply. Please refer below as u stated last mail:



characters #1-5 are numbers
characters #7-9 = "ABC"
Type is "A"
characters #1-5 are numbers
characters #7-9 = "DEF"
Type is "B"My comment: Yes the in front character i want 5 digit, middle ABC and as well at the back i also need total 5 digit. The type is correct that u mention

Jimmy,

I need your help to explain your example code 1 and 2. Can explain line by line? Thanks

apple
07-19-2007, 05:36 PM
Jimmy,

By the way is it i need to modify to run the macro? It seems cant run if i press F5 to run. Another thing please do explain the example coding that u post. Thanks a lot

apple
07-19-2007, 11:07 PM
Hi Jimmy,

Thanks a lot. The example coding VBA that you post firstly can works. Appreciated your help. Can u please explain for me start from:

For Each c In Rng.Cells untill the end coding that u write?

JimmyTheHand
07-19-2007, 11:29 PM
Hi Apple,
You got me confused again.


My comment: Yes the in front character i want 5 digit, middle ABC and as well at the back i also need total 5 digit.
Look at row #7 in the sample workbook you uploaded (post #14). It is
Sequence: 1.1..3
Number: 12356-ABC
Type: AThe number has no 5 digits after ABC. Yet, line #7 is highlighted in yellow. Why?

Similarly, numbers in rows 14 and 16 (12345-DEF and 45698-DEF) don't meet the criteria of 5 digits at the end, so why are they painted green?

apple
07-19-2007, 11:35 PM
Jimmy,

Sorry to make u confuse again. At the end number dont meet 5 number and i also highlight because my concern only in front 5 digit number , middle ABC. For at the back dont have 5 digit also nevermine. The important is criteria is A and the number is 12345-ABC or 78912-ABC-45678.

apple
07-19-2007, 11:45 PM
Jimmy,

Do get back me if u still confuse. Thanks

apple
07-20-2007, 12:24 AM
Jimmy,

Do get back me if u still confuse. Thanks

mdmackillop
07-20-2007, 12:45 AM
HI,

The reason i want highlight 2 different colour because i want to differenciate as explanation below:


1. Yellow colour: Type must A for middle word ABC, in front must 5 digit and as well 5 digit at the back.

Criteria: Must type A for the ABC middle character as well as 5 digit in front and at the back


2. Green colour type must B for middle DEF in front must 5 digit and as well 5 digit at the back.

Criteria: Must type B for the DEF middle character as well as 5 digit in front and at the back

Hope you will understand
Hi Apple
Please do NOT provide additional information by PM.
Your explanations are totally contrary to your example. There is no concept that we can programme thart corresponds to "nevermind".
I asked in my last post for specific information. We expect posters to reply to such requests in detail. This question is now 24 posts long, and I have no understanding that lets me make a start on any coding. Jimmy has been making some brave and fruitless attempts as far as I can see.
Anyway, I'm off for two weeks holiday. At present rate of progress, I'll look at this again on my return.
Good luck Jimmy!

JimmyTheHand
07-20-2007, 01:26 AM
Yes, MD has a point.
Your explanations are confusing. I guess this is partly due to difficulties with English. I'm not native myself, and could have misunderstood things because of that. Maybe you could ask an English speaking friend of yours, to help you with expressing your wishes?

Also, please understand that I am an employee, currently at work, and don't have that much free time, and even after work I usually have things to do. I will deal with your problem when I have the time, and your posting a reminder in every hour will not speed up the process. Please be patient.

I'll be back soon.

Jimmy

Charlize
07-20-2007, 01:45 AM
?Sub coloring()
Dim cell As Variant
For Each cell In Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row)
If Len(Split(cell, "-")(0)) = 5 Then
Select Case Split(cell, "-")(1)
Case "ABC"
cell.Offset(, -1).Resize(, 3).Interior.ColorIndex = 6
Case "DEF"
cell.Offset(, -1).Resize(, 3).Interior.ColorIndex = 35
End Select
End If
Next cell
End Sub

apple
07-20-2007, 01:57 AM
Noted. Thanks

JimmyTheHand
07-20-2007, 03:10 PM
Hi Apple,

putting together everything I have learned so far, I concluded the following:

You want to highlight in yellow all numbers for which these conditions are true
Type is "A"
characters #1-5 are numbers
characters #7-9 = "ABC"
it is not necessary to have more characters after ABC, but if there is anything there, it must be "-" and then 5 digits. For example:
12345-ABC --> is OK
12345-ABC-12345 --> is OK
12345-ABC-123456 --> is wrong, because the last part is 6 digits long.
12345-ABC-1234G --> is wrong, because the last part should be all numbers and here we have a G letter.And, you want to highlight in green all numbers for which the same conditions are true, except there must be Type B and middle "DEF" instead of Type A and middle "ABC".

First of all, I want you to tell me: is this the method, by which yellow and green lines are determined?

Assuming that this was the correct method, I modified the 2nd macro I posted earlier. Here is the updated code.
Sub Highlight2()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, i As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
S = c.Offset(, 1)
S = Replace(S, "~", "+")
For i = 0 To 9
S = Replace(S, CStr(i), "~")
Next
If (S = "~~~~~-ABC") And (c.Offset(, 2) = "A") Then c.Resize(, 3).Interior.ColorIndex = 6
If (S = "~~~~~-ABC-~~~~~") And (c.Offset(, 2) = "A") Then c.Resize(, 3).Interior.ColorIndex = 6

If (S = "~~~~~-DEF") And (c.Offset(, 2) = "B") Then c.Resize(, 3).Interior.ColorIndex = 35
If (S = "~~~~~-DEF-~~~~~") And (c.Offset(, 2) = "B") Then c.Resize(, 3).Interior.ColorIndex = 35
Next
End Sub
Test it on all numbers you can imagine, and tell me if it works the way you want it. If you say the code is all right, I will give a detailed explanation on how it works.

Jimmy

apple
07-21-2007, 10:20 PM
Hi,

Thanks. This is the answer that i want.
Yes, this is the method by which yellow and green lines are determined

apple
07-21-2007, 10:30 PM
Hi Jimmy,

Question 1

Can i ask u one more thing? How if the column that i want to fulfill the criteria is not sequence A,B,C but is something randomly for example column A,D,G?

Question 2

What if i want to highlight whole row once meet the criteria?

Thanks

JimmyTheHand
07-21-2007, 11:03 PM
Question 1

Can i ask u one more thing? How if the column that i want to fulfill the criteria is not sequence A,B,C but is something randomly for example column A,D,G?

Question 2

What if i want to highlight whole row once meet the criteria?
Hi Apple,

Answer 1
There are 4 code lines, similar to the one below, that are responsible for checking the criteria.
If (S = "~~~~~-ABC") And (c.Offset(, 2) = "A") Then c.Resize(, 3).Interior.ColorIndex = 6 The only thing to do is to change the "ABC" part to anything you want. Now, the code can be made flexible. For example, the user inputs the 3 letters, "ABC", "ADG", or whatever, and the code uses this input to change the criteria each time it runs. For this, an input method must be choosen. Two of the simplest options that spring to mind immediately:
using inputbox: the code will ask for the 3 letters each time it runs.
using a cell: the user inputs the 3 letters into a particular cell (EDIT: before running the code), and the code checks that cell for the info.There are several other methods, so if you want something more elegant, think it over and tell me.

Answer 2
In all 4 code lines that are responsible for checking the criteria (see above),c.Resize(, 3).Interior.ColorIndex = ... (number) must be replaced by
c.Entirerow.Interior.ColorIndex = ... (number)
Jimmy

apple
07-22-2007, 07:32 PM
Yes.I get what i want.Thank you very much

Question 1

1.Can i ask another question that arrange the column randomly as below?

For example:

In excel sheet, i want highlight column A, column D,column G, that arrange randomly and highlight entire row. Can you show me?

For example:

column A = 1.1
column D = 12345-DEF-45678
column G = B



Regards,
Apple

JimmyTheHand
07-23-2007, 11:21 AM
In excel sheet, i want highlight column A, column D,column G, that arrange randomly and highlight entire row. Can you show me?

For example:

column A = 1.1
column D = 12345-DEF-45678
column G = B
Hi Apple,

I don't understand, what you mean by "arrange randomly".
Based on the data structure I saw in your latest example, the code changes as below:

Sub Highlight2()
Dim Rng As Range, c As Range
Dim WS As Worksheet
Dim S As String, i As Long

Set WS = ActiveSheet
Set Rng = WS.Range("A1", WS.Range("A" & WS.Rows.Count).End(xlUp))
For Each c In Rng.Cells
S = c.Offset(, 3)
S = Replace(S, "~", "+")
For i = 0 To 9
S = Replace(S, CStr(i), "~")
Next
If ((S = "~~~~~-ABC") Or (S = "~~~~~-ABC-~~~~~")) And (c.Offset(, 6) = "A") Then c.EntireRow.Interior.ColorIndex = 6
If ((S = "~~~~~-DEF") Or (S = "~~~~~-DEF-~~~~~")) And (c.Offset(, 6) = "B") Then c.EntireRow.Interior.ColorIndex = 35
Next
End Sub
To change the relevant columns, I needed to change the Offset parameters. For example, cell in column B was c.Offset(, 1), while column D is c.Offset(, 3). Compare this version of my code with the previous one.
Also, In this version I contracted two If...Then code lines into one, to make the code shorter. This change is not related to your question, it is only an afterthought.

I'm still not sure if this is what you want, so please explain that "arrange randomly" expression.

Jimmy

apple
07-24-2007, 12:57 AM
Hi Jimmy,

Thank you. I get what i want. Please ignore the words randomly. Thank a lot.

JimmyTheHand
07-24-2007, 02:26 AM
Ok, you are welcome.

apple
07-25-2007, 07:07 AM
Hi Jimmy

Do need your advise if i want to add my number like this pattern:

Question 1

N4060-89732
That means in front N it can be any alphabet and follow by any digit and 5 digit number at the back. Conclusion in front must 5 digit and 5 digit at the back including alphabet

Question 2

5634B-89732

At the back B alphabet that i bold follow by any number and follow by 5 digit number. Conclusion in front must 5 digit and 5 digit at the back including alphabet

Question 3

H5634M-12345 in front one aplhabet that i bold follow by the 4 digit and then one alphabet M that i bold at the back. In front aplhabet and at the back alphabet it can be any alphabet

Question 4

34567-YZ436 middle YZcan be any alphabet. I want to highlight this kind of number in the middle. Conclusion in front must 5 digit and 5 digit at the back including alphabet

All is follow the criteria type A and type B that i stated last mail.

Jimmy,

Can i arrange my column this time in column A, columnB and columnH

That means column A = line
Column B = Item
Column H = type A or type B

Hope to hear from you soon as i keen to learn it. Thanks

JimmyTheHand
07-26-2007, 01:40 AM
Apple,

I placed our project on a new ground. Hopefully this will take care of your present and future needs.
See the attached workbook. It has two sheets: Data and Descriptor.
On Descriptor sheet, I listed all number patterns you have mentioned so far.

There are new 4 patterns listed in your last post, but you have said nothing about type, whether they are "A" or "B" or whatever. So I made a combination of all 4 patterns with both "A" and "B" types, as you can see in rows 6 to 13 of Descriptor sheet.

What you have to do:
Copy all your data (sequence, number and type) to the sheet called "Data", like I did in the attached example. Now, if you want to highlight numbers with any desired pattern, you have to do the following:
Go to Data sheet. Click on Highlight button. (The macro goes to Descriptor sheet, to show you the list of available patterns.)
Input the query type number of the desired pattern (you will find this query type number in column A of Descriptor sheet).After that, the macro goes back to sheet "Data", and highlights all rows where the number and type meet the given pattern. The color of highlight is defined by the colorindex number, in column D of Descriptor sheet.

If you want to create a new pattern, just fill the next empty row on Descriptor sheet. (Query Type numbers in column A are given already.)

If you want to create a pattern that has a fixed string inside, like 12345-ABC-67890, where ABC is fixed, start the pattern with * character, as you can see it in rows 2 to 5 (query type 1 to 4) on Descriptor sheet.

You can also change the columns where sequence, number and type data are located. In your last request, you wanted to work with columns A, D and G. From now on, you can work with any columns, but you will need to modify the code a little bit.
Find this part of code on Module1
Sub Main()
Sheets("Descriptor").Activate
Highlight2 InputBox("Select <Query type> from Column A", , "1"), "A-D-G"
End Sub To work with a different column-sequence, change the "A-D-G". For example, to work with columns B, E and H change the code this way:
Sub Main()
Sheets("Descriptor").Activate
Highlight2 InputBox("Select <Query type> from Column A", , "1"), "B-E-H"
End Sub
Cheers,

Jimmy

apple
07-26-2007, 02:14 AM
HI,
Thanks. It's look very good.

But if i dont want the query toolbar and i only want once user key in the pattern number that i stated, i will straight highlight. Yes, you are right. All the type is type A or type B.

Jimmy,

Please show me the way to write coding for the pattern number that i stated above. If i want 1 alphabet in front and 1 alphabet at the back, how to write the syntax? Thanks and have a nice day

JimmyTheHand
07-26-2007, 02:26 AM
But if i dont want the query toolbar and i only want once user key in the pattern number that i stated, i will straight highlight.

This I don't understand.


Please show me the way to write coding for the pattern number that i stated above. If i want 1 alphabet in front and 1 alphabet at the back, how to write the syntax? Thanks and have a nice day

You don't need coding. You just create a new pattern in column B of Descriptor sheet, then use the code I provided.
The new pattern can be anything that would be good as an example here.
If you want 1 alphabet, 4 digits, dash, 4 digits, 1 alphabet pattern, then write A1234-4567B, or X2533-2553Y, or anything similar in column B of Descriptor sheet.

apple
07-26-2007, 04:35 PM
But if i dont want the query toolbar and i only want once user key in the pattern number that i stated, i will straight highlight.http://www.vbaexpress.com/forum/images/quotes/quot-bot-left.gifhttp://www.vbaexpress.com/forum/images/quotes/quot-bot-right.gifThis I don't understand.

Hi Jimmy,

Thanks for your function. Appreciated your help.

Is it u dont understand the above quote? In this quote i mean that, I only want to straight highlight without fill in the query type. I still study your way how to edit to change once the the user enter any model pattern number, it will straight highlight. Can you show me? The attachment is what i edit and still error. Thank ya. Hope to hear you soon.

apple
07-26-2007, 09:10 PM
Hi, Jimmy,

This is the attachment i edit. Please do help me to see my coding but i still got error. I want to edit once user enter the item number in variety pattern that i stated above it will straight highlight if user press higlight button. I do hide certain coding that u written for me because i dont want query type text box pop out. Thanks. Hope to hear you soon.

apple
07-26-2007, 09:13 PM
Hi,

Pattern that i means is:

N4060-89732
That means in front N it can be any alphabet and follow by any digit and 5 digit number at the back. Conclusion in front must 5 digit and 5 digit at the back including alphabet



5634B-89732

At the back B alphabet that i bold follow by any number and follow by 5 digit number. Conclusion in front must 5 digit and 5 digit at the back including alphabet



H5634M-12345 in front one aplhabet that i bold follow by the 4 digit and then one alphabet M that i bold at the back. In front aplhabet and at the back alphabet it can be any alphabet



34567-YZ436 middle YZcan be any alphabet. I want to highlight this kind of number in the middle. Conclusion in front must 5 digit and 5 digit at the back including alphabet

apple
07-26-2007, 11:51 PM
Hi Jimmy,

Let's me explain for you my purpose to do such pattern. It it because the pattern is refer to item model. So each time, there is different type of model and alphabet. Thanks

JimmyTheHand
07-27-2007, 01:25 AM
Hi Apple,

I'm sorry, I don't understand what you want. I'm just guessing around.
I have these questions, please answer them carefully.
Q1.
What is the immediate action the user must take to start the program?
(e.g. clicking a button, writing into a particular cell, etc.)

Q2.
How many patterns do you want highlighted when the user takes that certain action I asked about in Question#1? Do you want to highlight only one pattern? Or do you want to highlight all the defined patterns? Or else?

Q3.
If you want to highlight only one pattern, or a limited number of patterns, how do you want to define, or how will the user define, which patterns must be highlighted?


In the meantime, I made another ("brave and possibly futile") attempt to satisfy your need. I changed the code again, considerably. In this version, the user clicks the button called "Highlight" on the Data sheet, and so he starts the program. Patterns are defined on Descriptor sheet. If a patterns has a "Y" in column A of the Descriptor sheet (meaning "yes, I want to highlight items of this model"), then the numbers corresponding to this pattern will be highlighted.

Please see the attached workbook. Look at Descriptor sheet. There you see the patterns in column B. Numbers are represented by "#", while letters of the alphabet are represented by "$". In other words, "#" in the pattern can mean any number, and "$" can mean any letter of the alphabet. Any other characters in the pattern (e.g. "-", "A", "B", etc.) are fixed. Color codes are located in column D. Type letters are located in column C.

Using this Descriptor sheet you can define any patterns you want.
Placing "y" into column A will make it highlight when you next click on Highlight button.

Jimmy

apple
07-27-2007, 03:35 AM
Hi Jimmy,

Thanks for your afford. Please do see below for my answer

Q1.
What is the immediate action the user must take to start the program?
(e.g. clicking a button, writing into a particular cell, etc.)

Answer: Firstly user must put all the item number and type in the "DATA" sheet . Then click the button highlight. It's will auto highlight.

Q2
How many patterns do you want highlighted when the user takes that certain action I asked about in Question#1? Do you want to highlight only one pattern? Or do you want to highhlight all the defined patterns? Or else?

Anwser:
Highlight all the define pattern: Please see below:

a) 14567-89754
b) A2134-56481
c) 2134B-45123 .
d) A5478B-43561
e) A3451BC -45673
f) AB1342C-12345
h)1242AB-12345
g) 12345-FAH123


Q3.
If you want to highlight only one pattern, or a limited number of pattern , how do you want to define, or how will the user define, which patterns must be highlighted?
User already know that highlighted function is follow criteria number as i stated.

Thanks a kot and have a nive day

apple
07-27-2007, 03:36 AM
Hi Jimmy,

Thanks for your afford. Please do see below for my answer

Q1.
What is the immediate action the user must take to start the program?
(e.g. clicking a button, writing into a particular cell, etc.)

Answer: Firstly user must put all the item number and type in the "DATA" sheet . Then click the button highlight. It's will auto highlight.

Q2
How many patterns do you want highlighted when the user takes that certain action I asked about in Question#1? Do you want to highlight only one pattern? Or do you want to highhlight all the defined patterns? Or else?

Anwser:
Highlight all the pattern that written in coding . I no need listed in sheet descriptor. Once user put in sheet "DATA" the information. It's straight forward highlighted. So, i only want "sheet data". Please see below:

a) 14567-89754
b) A2134-56481
c) 2134B-45123 .
d) A5478B-43561
e) A3451BC -45673
f) AB1342C-12345
h)1242AB-12345
g) 12345-FAH123


Q3.
If you want to highlight only one pattern, or a limited number of pattern , how do you want to define, or how will the user define, which patterns must be highlighted?
User already know that highlighted function is follow criteria number as i stated.

Thanks a lot and have a nive day

JimmyTheHand
07-27-2007, 04:50 AM
Hi

As you wished, I hardcoded the 8 patterns listed in your last post, though my personal opinion is that hardcoding such things is unwise. But you surely know why you want it this way.

Each patterns have "A" and "B" types, so there are actually 8*2=16 patterns altogether. You can add more patterns later, in the FillArray sub. Don't forget to change the number of patterns at the top of the module:
Option Explicit
Const NumberOfPatterns = 16
I hope this is it.

Jimmy

apple
07-27-2007, 09:37 PM
Hi Jimmy,

Thank you very much. Yes, this is the one i want.In the meantime i will study first the coding that you written. Appreciated your effort and time to help me.

Thank again and have a nice day. Take care :giggles:


Regards,
Apple

Aussiebear
07-28-2007, 05:59 AM
a) 14567-89754
b) A2134-56481
c) 2134B-45123 .
d) A5478B-43561
e) A3451BC -45673
f) AB1342C-12345
h)1242AB-12345
g) 12345-FAH123


G'day Apple. I must say you most of us confused with your initial couple of posts. Luckily Jimmy has seemed to grasp your request. You might like to take the following information on board when considering future posts.

In the quoted section above there are what we refer to as 8 strings, namely a to g.

a) is numerical, and the others are alphanumerical.

If you were to refer to anyone of the characters, it would be of assistance to us if you indicated the position of a character which you are dealing with, rather than referring to front or back.


34567-YZ436 middle YZcan be any alphabet. I want to highlight this kind of number in the middle. Conclusion in front must 5 digit and 5 digit at the back including alphabet


You have often made mention of 5 digit in front and 5 digit at the back as being necessary. Yet in the above example of strings, D,E,F,H,G all have at least one occurance of a 6 character section. One in fact has 7 and another has 8 (including the space). This seems to contradict your stated aim of 5 digit in front or 5 digit at back. As Jimmy indicated earlier there is at least 16 patterns. Can you understand why most people would be reluctant to assist here?

It great to see you posting here, but perhaps a bit more thought about the terminology, may well have generated more responces to your query. Jimmy has done a magnificent job in decifering your questions to date.

I look forward to your next post and have a great day.

apple
07-28-2007, 07:09 AM
Hi Aussiebear,

Good day to you too. Yes, next time will explain carefully. Anyway thanks for your respond

apple
07-28-2007, 07:15 AM
Hello Jimmy,

How are you? Jimmy, please do help me to explain the below red colour font:

ColSeq = Left(ColumnSequence, InStr(ColumnSequence,"-") - 1)
ColType = Mid(ColumnSequence, InStrRev(ColumnSequence, "-") + 1)
ColNum = Mid(ColumnSequence, InStr(ColumnSequence, "-") + 1, InStrRev(ColumnSequence, "-") - InStr(ColumnSequence, "-") - 1)


If InStr(S, "#") > 0 Or InStr(S, "$") > 0 Then MsgBox "The number in row " & c.Row & " contains # or $ character. This may result in false highlight", vbOKOnly, "Warning"
For i = 1 To Application.WorksheetFunction.Min(Len(NumPattern), Len(S))
Select Case Mid(NumPattern, i, 1)
Case "#"
If (Asc(Mid(S, i, 1)) > 47) And (Asc(Mid(S, i, 1)) < 59) Then S = Left(S, i - 1) & "#" & Mid(S, i + 1)
Case "$"
If (Asc(Mid(S, i, 1)) > 64) And (Asc(Mid(S, i, 1)) < 91) Then S = Left(S, i - 1) & "$" & Mid(S, i + 1)
If (Asc(Mid(S, i, 1)) > 96) And (Asc(Mid(S, i, 1)) < 123) Then S = Left(S, i - 1) & "$" & Mid(S, i + 1)
End Select


Thanks

JimmyTheHand
07-29-2007, 01:05 PM
Hi Apple,

here is some explanation on the code.

Part 1.

Sub Highlight2(ColumnSequence As String, NumPattern As String, NumType As String, ColorCode As Long)
[...]
1. ColSeq = Left(ColumnSequence, InStr(ColumnSequence,"-") - 1)
2. ColType = Mid(ColumnSequence, InStrRev(ColumnSequence, "-") + 1)
3. ColNum = Mid(ColumnSequence, InStr(ColumnSequence, "-") + 1, InStrRev(ColumnSequence, "-") - InStr(ColumnSequence, "-") - 1)
'(code lines are numbered for further reference)

If you noticed, Sub Highlight2 has 4 parameters, one of them is called ColumnSequence. This parameter describes which 3 columns contain the data. In your latest example workbooks it was columns A, D and G. But earlier you posted exaples where data was in columns A, B and C. I designed the code to be able to handle such changes later, with minimal modification.
In Sub Main, there is this line of code (with ColumnSequence parameter highlighted in red):
Highlight2 "A-D-G", Patterns(i).PNumber, Patterns(i).PType, Patterns(i).PColor If you later have worksheets where data is in other columns, you only need to change this "A-D-G" parameter to whatever is necessary.

The code part you have asked about breaks down ColumnSequence parameter to column letters.
Line 1. This string expression returns what is to the left of the first "-" character in the string ("A-D-G").
Result: ColSeq = "A" (Sequence numbers are in column A.)

Line 2. This string expression returns what is to the right of the last "-" character in the string ("A-D-G").
Result: ColType = "G" (Type letters are in column G.)

Line 3. The string expression in line with ColNum is what is between the first and the last "-" characters in the string. ("A-D-G")
Result: ColNum = "D" (Numbers are in column D.)
These 3 string variables (ColSeq, ColType, ColNum) are used in later parts of the code to refer to columns A, D and G.
Please see VBA Help for explanations on Left, Mid and Instr functions.

Part 2.
For i = 1 To Application.WorksheetFunction.Min(Len(NumPattern), Len(S))
Select Case Mid(NumPattern, i, 1)
Case "#"
If (Asc(Mid(S, i, 1)) > 47) And (Asc(Mid(S, i, 1)) < 59) Then S = Left(S, i - 1) & "#" & Mid(S, i + 1)
Case "$"
If (Asc(Mid(S, i, 1)) > 64) And (Asc(Mid(S, i, 1)) < 91) Then S = Left(S, i - 1) & "$" & Mid(S, i + 1)
If (Asc(Mid(S, i, 1)) > 96) And (Asc(Mid(S, i, 1)) < 123) Then S = Left(S, i - 1) & "$" & Mid(S, i + 1)
End Select
Next
This is a loop, going through all characters in the current "number" string and the current pattern. Steps:
Step 1:
Examines the 1st character of the pattern. In case it is #, it means that numeric character is expected in the number string. In case it is &, it means that alphabetical character is expected in the number string.
Step 2:
If the 1st character is numeric, and numeric is expected, then it is changed to #
If the 1st character is alphabetical, and alphabetical character is expected, then it is changed to $
Step 3:
Go to the next character.

Let's see it with an example. Let's say that the number is 12345-ADC12
and the pattern is #####-#####
Setp 1:
1st character of the pattern is "#", so a numeric character is expected in the string.
Step 2:
1st character of the string is "1", and numeric character is expected, so the "1" is replaced by "#", and the string changes to "#2345-ADC12"

In further steps of the loop, characters of the string are replaced in a similar way, until, at the end, the string becomes "#####-ADC##"
Characters "ADC" are not changed, because the patters had only numerical charcters.

In the end, the changed string ("#####-ADC##") and the pattern (#####-#####) are compared. If they are identical, then the original number string is an analog of the pattern.

Close-up about the code:

If (Asc(Mid(S, i, 1)) > 47) And (Asc(Mid(S, i, 1)) < 59) Then S = Left(S, i - 1) & "#" & Mid(S, i + 1) Mid(S, i, 1) is the i-th character of the string. It is checked against being a numerical character via it's character (ASC) code. ASC codes of numerical characters are 48,49,50, ... 58 (corresponding to 0, 1, 2, ... 9)
If such a character is found in the string, it is replaced by a #.

Similarly, alphabetical characters are replaced by $
ASC codes of alphabetical characters are 65...90 (as A...Z) and 97...122 (as a...z)

Hope this helps.

Jimmy

apple
07-29-2007, 10:13 PM
Hi Jimmy,

Thanks for your explanation

apple
07-29-2007, 10:28 PM
Ok, thanks

JimmyTheHand
07-30-2007, 12:58 AM
Apple,

No offense intended, but I must tell you something.
Your last problem took me a week to solve. This is very bad, because the problem itself would have needed only 1 hour or so. The remaining 6 days and 23 hours were spent on my trying to understand what you want to (but can not) tell.

I was glad to be able to decipher your previous idea and finish the job. But now, here you are with another question, and your explanation is not an inch clearer than before. I'm sorry to say but I don't want to spend another week staring out of my head and wondering about what you really want to accomplish.

I don't mind at all that you are new to VBA, this forum is for those who want to learn VBA, after all. But this problem here is not related to your VBA skill. It's just that you can't express yourself.

So, my first advice on this new topic is that you find a friend who speaks English better than you, and have him help you rephrasing your question and explanation. In case such a friend is unavailable, I'm still willing to help, but it would be better for everyone if you made some progress with your language skill.

Jimmy

apple
07-30-2007, 01:08 AM
Jimmy

Ok, thanks