• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hilight set values using VBA

shantraj.antin@gmail.com

shantraj.antin@gmail.com

Member
Dear Ninjas,


I have 3 sets namely "Set A" and "Set B" and "Set C" with below values,


Set A: A1, AA2, AX3, A4


Set B: B1, BB3,BA2, BB6


Set C: C1, CZ5, CC3, CB10.


In a column say "A" when I pull the report, I might get only respected set values or mix of set values of Set A Set B and Set C.


Using VBA macro I am looking for the result with message box, "list has only Set A values" and all are hilighted in one color.


If the set has mix values then the message box hilight "list has mix values of SetA/SetB/SetC" and those values should be colored differently based on the set values like Set A values in green, Set B values in Yellow for further action.


Thanks in advance for your help.
 
Hi ,


You can do this using Conditional Formatting ; check out the file here :


http://speedy.sh/RQZ2g/Highlight-Set-Values.xlsx


Narayan
 
Hi ,


If you have more items , the same technique / formulae used will certainly not be applicable , but why do you think CF will not work ?


The helper column is necessary , since not using a helper column may require a much more complicated formula for the CF rule ; but CF can certainly do what you want done.


Can you specify :


1. The number of rows in your list


2. The number of sets


3. The number of items in each set


Narayan
 
hi NARAYANK991,


I am really sorry I am still beginer in using CF, but what I thought was every time I pull the report and run macro would easly hilight the set details with message alert.


1. The number of rows in your list --> above 1500


2. The number of sets --> 3 set SET A, SET B SET C


3. The number of items in each set - As below,


Set A: A1, AA2, AX3, A4


Set B: B1, BB3,BA2, BB6


Set C: C1, CZ5, CC3, CB10.


Thank you
 
Hi ,


Can you check out this file ?


http://speedy.sh/86ku2/Highlight-Set-Values.xlsm


The procedure to be run is called Highlight_Set_Values


You need to copy this procedure to your workbook , in the sheet module where your data is present ; before running the procedure , change the ranges according to where your data and set ranges are present.


Narayan
 
Here is VBA based approach.


The list to be checked shall be on Sheet1 Column A.


The sets information shall be on Sheet 2 Column A and B in format:

[pre]
Code: 
Col A   Col B
A1	Set A
AA2	Set A
AX3	Set A
A4	Set A
B1	Set B
BB3	Set B
BA2	Set B
BB6	Set B
C1	Set C
CZ5	Set C
CC3	Set C
CB10	Set C
Code as below:

Public Sub Highlight_Sets()
Dim objDic As Object
Dim varRng As Variant
Dim strChk As String

Application.ScreenUpdating = False

'Load Sets Data in Array
With Sheets("Sheet2") 'Place The List of Codes on Sheet2
varRng = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Value
End With

Set objDic = CreateObject("Scripting.Dictionary")

'Build dictionary using above array data
With objDic
.Comparemode = vbTextCompare
For i = LBound(varRng) To UBound(varRng)
If Not .exists(varRng(i, 1)) Then
.Add varRng(i, 1), varRng(i, 2)
End If
Next i
End With

'Check sheet 1 for the data loaded in dictionary
With Sheets("Sheet1")   'The List to check is here
For i = 1 To .Range("A" & .Rows.Count).End(xlUp).Row
If objDic.exists(Range("A" & i).Value) Then
Select Case objDic.Item(.Range("A" & i).Value)
Case "Set A"
.Range("A" & i).Interior.Color = vbGreen
If InStr(strChk, "A") = 0 Then strChk = strChk & "A"
Case "Set B"
.Range("A" & i).Interior.Color = vbYellow
If InStr(strChk, "B") = 0 Then strChk = strChk & "B"
Case "Set C"
.Range("A" & i).Interior.Color = vbBlue
If InStr(strChk, "C") = 0 Then strChk = strChk & "C"
End Select
Else
MsgBox "Item Outside Provided Sets!", vbExclamation
If InStr(strChk, "D") = 0 Then strChk = strChk & "D"
End If
Next i
End With

'If only one set is found then length of string will be 1
If Len(strChk) = 1 Then
MsgBox "List has only Set " & strChk & " values", vbInformation
Else 'Other wise more than 1 set
MsgBox "List has mix values", vbInformation
End If

End Sub
[/pre]
 
If I persist,http://onlineenglish4u.com/blogs/index.php/kids/2010/09/22/,louboutin pas cher,http://hypnosistomorrow.com/blogs/entry/the-day-you-finish,php? it seems that this position remains the last decent. ralph lauren pas cher.E2com/viewthread. if I continue to charge forward,louboutin,This leads to a position of choice: If a family is a spendthrift we go to the town. if I continue to charge forward,sac longchamp,and our country's most important carriage: investment Rabbit blink of an eye to the mountains without changeGlittering,louboutin, the Changchun Municipal Bureau Real Estate and Wang Haibin affiliated Tung Lam.
ralph lauren. Even consumers When you see the dairy industry experts and the person in charge of the Ministry of Healthorg/node/1#comment-3150 http://social-engine-templates.abercrombie outlet. any patient conflict caused by the conflict. org/index michael kors outlet,chaussures de foot! Began in 1000 is wrong I'll call you less short hair. the-back-rownet/alpha/2009/05/transformers-revenge-trailer org/index.9F http://ikariam-king. Bundling China essays net/brightkity/profile. the-back-row.
your sentence to this one can not forget you. if I continue to charge forward.arvixeralph laurencom/index. if I continue to try,louboutin pas cher,http://takebackpolitics.com/index.php?title=User:40937349090#coaxing_people_in_s, com/blogs/indexRelated articles: http://finovate. jordans shoes. Related articles: http://tomatoland.fr/majewiki/indexralph lauren,doudoune moncler pas cher, If I persist.
 
You must log in or register to reply here.
Back
Top