compare two arrays a1:a7 compare to b1:b7, count how many are the same?
- Thread starter henksss
- Start date
Here a code using the change event of the sheet (Each change of values in A:K, the code will automatically executed) And also using colors you choosed
[pre]
[/pre]
Here you file with code http://speedy.sh/PbjQn/Conditional-format-vb.xls
Regards
[pre]
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, UsedRange, Range("A:K")) Is Nothing Then CondForm
End Sub
Private Sub CondForm()
Dim ClrInd As Integer
Dim c As Range
Dim ArrayColor
ArrayColor = Array(9, 37, 35, 36, 38, 7, 3)
For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("L:L"))
ClrInd = xlNone
Select Case c.Value
Case ""
Case 0 To 6: ClrInd = ArrayColor(c.Value)
End Select
c.Interior.ColorIndex = ClrInd
Next c
End SubHere you file with code http://speedy.sh/PbjQn/Conditional-format-vb.xls
Regards
What I asked for now works but what I have asked for is not exactly what I need.
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
SirJB7
Excel Rōnin
@Debraj Roy, mercatog
Hi!
Regarding:
http://chandoo.org/forums/topic/compare-two-arrays-a1a7-compare-to-b1b7-count-how-many-are-the-same#post-38859
http://chandoo.org/forums/topic/compare-two-arrays-a1a7-compare-to-b1b7-count-how-many-are-the-same#post-38864
Would you be as kind as including the concordant English translation when you happen to write in other language? Take care that people who might read those comments surely will feel annoyance for couldn't understand the actual and complete meaning of the post. Thank you.
I know I've done it myself a couple of times before and so I realized about the inconvenience I could have caused.
Regards!
Hi!
Regarding:
http://chandoo.org/forums/topic/compare-two-arrays-a1a7-compare-to-b1b7-count-how-many-are-the-same#post-38859
http://chandoo.org/forums/topic/compare-two-arrays-a1a7-compare-to-b1b7-count-how-many-are-the-same#post-38864
Would you be as kind as including the concordant English translation when you happen to write in other language? Take care that people who might read those comments surely will feel annoyance for couldn't understand the actual and complete meaning of the post. Thank you.
I know I've done it myself a couple of times before and so I realized about the inconvenience I could have caused.
Regards!
Can someone help?
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
Hi thanks for your good question
If L=0 and M=0 then (ignore it)
If L=0 and M=1 then (ignore it)
If L=0 and M=2 then (ignore it)
if L=1 and M=1 then (ignore it)
if L=2 and M=1 then (ignore it)
if L=3 and M=0 then (ignore it)
I have these results pretty well ignored with my original message,
If L=0 and M=0 then (ignore it)
If L=0 and M=1 then (ignore it)
If L=0 and M=2 then (ignore it)
if L=1 and M=1 then (ignore it)
if L=2 and M=1 then (ignore it)
if L=3 and M=0 then (ignore it)
I have these results pretty well ignored with my original message,
Can someone help?
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
What I need is this
conditional format; in excell 2003
if(and(L3:L1000=1,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=2,M3:M1000=2),format colour blue in both columns L,M
if(and(L3:L1000=3,M3:M1000>0),format colour green in both columns L,M
if(and(L3:L1000=4,M3:M1000=>0),format colour yellow in both columns L,M
if(and(L3:L1000=5,M3:M1000=>0),format colour pink in both columns L,M
if(and(L3:L1000=6,M3:M1000=>0),format colour red in both columns L,M
As you can see there are 6 conditional formats but on the toolbat conditional format it only allows 3 conditions.
So thats why I need to have code in VB giving me these formats.
Columns L and M have formulas in them;
column L has =IF(D363="","",SUMPRODUCT(COUNTIF(D363:I363,$A$1:$N$1)))
column M has =IF(D363="","",SUMPRODUCT(COUNTIF(J363:K363,$A$1:$N$1)))
So when column L or M have ""due to the start of its formula being =IF(D363="","", it has no colour but as soon it becomes a value the colour needs to change automatically.
Regards Henk
SirJB7
Excel Rōnin
Hi, kenksss!
I think that people who're surely following this topic might have yet read your two last comments, so no need to paste them again, I think, as there was only 1 day of difference between the two pairs.
May I refer you to the 3rd. green sticky post at this forums main page, 13th. and 17th. paragraphs?
"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight.
...
If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."
Hope you get your answer soon.
Regards!
I think that people who're surely following this topic might have yet read your two last comments, so no need to paste them again, I think, as there was only 1 day of difference between the two pairs.
May I refer you to the 3rd. green sticky post at this forums main page, 13th. and 17th. paragraphs?
"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight.
...
If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."
Hope you get your answer soon.
Regards!
Thanks for your advise, I take this onboard as good advise. I was actually wondering if my first post didn't get posted. I therefore thought I would try it again.
I was getting very prompt reply initially and as it slowed somewhat thought there could be something wrong with my posting. I guess I was being a little anxious, sorry about that no harm meant.
Thanks very much
Regards Henk
I was getting very prompt reply initially and as it slowed somewhat thought there could be something wrong with my posting. I guess I was being a little anxious, sorry about that no harm meant.
Thanks very much
Regards Henk
NARAYANK991
Excel Ninja
Hi Henk ,
I am sorry but in case your requirement is urgent , can you upload your workbook ? Going by whatever help has already been given , I am not able to understand your requirement.
Narayan
I am sorry but in case your requirement is urgent , can you upload your workbook ? Going by whatever help has already been given , I am not able to understand your requirement.
Narayan
SirJB7
Excel Rōnin
Hi, henksss!
Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
For uploading guidelines give a look at the second one.
Regards!
Perhaps you'd want to read the three green sticky posts at this forums main page so as to know the guidelines that will lead to know how this community operates (introducing yourself, posting files, netiquette rules, and so on).
For uploading guidelines give a look at the second one.
Regards!
SirJB7
Excel Rōnin
Hi, henksss!
Give a look at this file:
https://dl.dropbox.com/u/60558749/compare%20two%20arrays%20a1_a7%20compare%20to%20b1_b7%2C%20count%20how%20many%20are%20the%20same_%20-%20conditional%20format%20vb%20%28for%20henksss%20at%20chandoo.org%29.xlsx
Select range L3:M1001, Start tab, Styles group, Conditional Formatting icon, and check the 5 conditions set there. For column L values of 4 thru 6 there's no need to consider M column value as it wouldn't be zero never, so I simplified the conditions.
I edited the P4:Q5 range to change 1000 to 1001 row values.
Just advise if any issue.
Regards!
PS: I don't read the whole topic, just checked your uploaded file, so I don't know what's all these about but... two things:
a) you should have warned users about eye damage... nice colors, ha ha...
b) if gonna use it for betting or gambling, I'd be in... take my 15% as downpayment
Give a look at this file:
https://dl.dropbox.com/u/60558749/compare%20two%20arrays%20a1_a7%20compare%20to%20b1_b7%2C%20count%20how%20many%20are%20the%20same_%20-%20conditional%20format%20vb%20%28for%20henksss%20at%20chandoo.org%29.xlsx
Select range L3:M1001, Start tab, Styles group, Conditional Formatting icon, and check the 5 conditions set there. For column L values of 4 thru 6 there's no need to consider M column value as it wouldn't be zero never, so I simplified the conditions.
I edited the P4:Q5 range to change 1000 to 1001 row values.
Just advise if any issue.
Regards!
PS: I don't read the whole topic, just checked your uploaded file, so I don't know what's all these about but... two things:
a) you should have warned users about eye damage... nice colors, ha ha...
b) if gonna use it for betting or gambling, I'd be in... take my 15% as downpayment
SirJB7
Excel Rōnin
Hi, henksss!
I noticed that your C column is manually greened on its background for even rows. Instead of copying pairs of White(Uncolored)/Green cells you can apply a CF as this:
- select column C
- go to CF, Add New Rule
- select last option, Formula
- type this formula:
=AND(ROW()>2,A1<>"",MOD(ROW(),2)=0)
- select green background
Would work too selecting C3:C1001 range and changing formula to:
=AND(A3<>"",MOD(ROW(),2)=0)
Regards!
I noticed that your C column is manually greened on its background for even rows. Instead of copying pairs of White(Uncolored)/Green cells you can apply a CF as this:
- select column C
- go to CF, Add New Rule
- select last option, Formula
- type this formula:
=AND(ROW()>2,A1<>"",MOD(ROW(),2)=0)
- select green background
Would work too selecting C3:C1001 range and changing formula to:
=AND(A3<>"",MOD(ROW(),2)=0)
Regards!
SirJB7
Excel Rōnin
Hi, henksss!
I followed the link and got the file open with Excel. I saved it and uploaded again. Please try downloading it once more from same previous link, which I'm copying here:
https://dl.dropbox.com/u/60558749/compare%20two%20arrays%20a1_a7%20compare%20to%20b1_b7%2C%20count%20how%20many%20are%20the%20same_%20-%20conditional%20format%20vb%20%28for%20henksss%20at%20chandoo.org%29.xlsx
Regards!
I followed the link and got the file open with Excel. I saved it and uploaded again. Please try downloading it once more from same previous link, which I'm copying here:
https://dl.dropbox.com/u/60558749/compare%20two%20arrays%20a1_a7%20compare%20to%20b1_b7%2C%20count%20how%20many%20are%20the%20same_%20-%20conditional%20format%20vb%20%28for%20henksss%20at%20chandoo.org%29.xlsx
Regards!