Increasing Conditional Formatting from 3 to 5
 

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Use a formula to work out the conditional format, Use if With Or in, and you
onyl need to use one of the 3 formats.

If you need more help just say

"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Hi Tom, Thanks for the response, unfortunately I tried to set this formula
but it wouldn't accept it saying that I cannot use unions, intersections, or
array constants, do you have an example? should this go in the first
condition's area box?

Thanks
Cathy


"Tom Hewitt" wrote:

Use a formula to work out the conditional format, Use if With Or in, and you
onyl need to use one of the 3 formats.

If you need more help just say

"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Here's some modified code from an old Frank Kabel post. It assumes your
"other set of numbers" is in E1:E5 and the ones you want to match to them are
in A1:A5........of course those ranges and the colors can be modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Thank you

I added in your code as instructed and amended the cell details, the
spreadsheet stayed blank, I changed them round to see if it would make a
difference - but again nothing. What am I doing wrong?

Cathy

"CLR" wrote:

Here's some modified code from an old Frank Kabel post. It assumes your
"other set of numbers" is in E1:E5 and the ones you want to match to them are
in A1:A5........of course those ranges and the colors can be modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Without the details, there are so many possibilities.......some of which
include, that your "numbers" are not both really numbers, but that one set is
actually TEXT formatted, or with leading or trailing spaces,
etc..............another might be that you didn't put the code in the proper
WorkSheet module for the sheet you are working on.........another might be a
type-o in the modifications, etc etc etc.........
Maybe if you post your modified code back, and tell us where it's located,
we can help more.

I tested this code in a sample workbook and it worked fine here in my XL97.
Try opening a new workbook, pasting the code in the Sheet1 module, putting
your list in E1:E5 of Sheet1, and typing your numbers to match in A1:A5 of
Sheet1....just as a test....A1:A5 cells should change color accordingly.

Post back if you still have difficulties....

Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Thank you

I added in your code as instructed and amended the cell details, the
spreadsheet stayed blank, I changed them round to see if it would make a
difference - but again nothing. What am I doing wrong?

Cathy

"CLR" wrote:

Here's some modified code from an old Frank Kabel post. It assumes your
"other set of numbers" is in E1:E5 and the ones you want to match to them are
in A1:A5........of course those ranges and the colors can be modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

Hiya, I opened a new spreadsheet as you instructed and put the code in sheet
1, this is the code I copied and pasted:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I entered the numbers in A1-A5 and E1-E5 and 4 of them should have matched
up, but nothing happened - what did I do wrong?

Thanks for all your help with this, I really appreciate it
Cathy


"CLR" wrote:

Without the details, there are so many possibilities.......some of which
include, that your "numbers" are not both really numbers, but that one set is
actually TEXT formatted, or with leading or trailing spaces,
etc..............another might be that you didn't put the code in the proper
WorkSheet module for the sheet you are working on.........another might be a
type-o in the modifications, etc etc etc.........
Maybe if you post your modified code back, and tell us where it's located,
we can help more.

I tested this code in a sample workbook and it worked fine here in my XL97.
Try opening a new workbook, pasting the code in the Sheet1 module, putting
your list in E1:E5 of Sheet1, and typing your numbers to match in A1:A5 of
Sheet1....just as a test....A1:A5 cells should change color accordingly.

Post back if you still have difficulties....

Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Thank you

I added in your code as instructed and amended the cell details, the
spreadsheet stayed blank, I changed them round to see if it would make a
difference - but again nothing. What am I doing wrong?

Cathy

"CLR" wrote:

Here's some modified code from an old Frank Kabel post. It assumes your
"other set of numbers" is in E1:E5 and the ones you want to match to them are
in A1:A5........of course those ranges and the colors can be modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any that
match another set of 5 numgers that will change week on week (lottery
numbers). I can set the conditional formatting to highlight any that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

If you actually got the code where it's to go, by right clicking on the
sheet1 tab and selecting View Code, and pasteing the code in the large
window on the right......and pasteing it ALL there, including the top line
which you didn't include here......and it still don't work , then all I can
suggest is that you send your workbook to my personal addy, NOT to the
newsgroup, and I will take a look at it for you......

Vaya con Dios,
Chuck, CABGx3


"Evans9939" wrote in message
...
Hiya, I opened a new spreadsheet as you instructed and put the code in
sheet
1, this is the code I copied and pasted:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I entered the numbers in A1-A5 and E1-E5 and 4 of them should have matched
up, but nothing happened - what did I do wrong?

Thanks for all your help with this, I really appreciate it
Cathy


"CLR" wrote:

Without the details, there are so many possibilities.......some of which
include, that your "numbers" are not both really numbers, but that one
set is
actually TEXT formatted, or with leading or trailing spaces,
etc..............another might be that you didn't put the code in the
proper
WorkSheet module for the sheet you are working on.........another might
be a
type-o in the modifications, etc etc etc.........
Maybe if you post your modified code back, and tell us where it's
located,
we can help more.

I tested this code in a sample workbook and it worked fine here in my
XL97.
Try opening a new workbook, pasting the code in the Sheet1 module,
putting
your list in E1:E5 of Sheet1, and typing your numbers to match in A1:A5
of
Sheet1....just as a test....A1:A5 cells should change color accordingly.

Post back if you still have difficulties....

Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Thank you

I added in your code as instructed and amended the cell details, the
spreadsheet stayed blank, I changed them round to see if it would make
a
difference - but again nothing. What am I doing wrong?

Cathy

"CLR" wrote:

Here's some modified code from an old Frank Kabel post. It assumes
your
"other set of numbers" is in E1:E5 and the ones you want to match to
them are
in A1:A5........of course those ranges and the colors can be
modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context
menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any
that
match another set of 5 numgers that will change week on week
(lottery
numbers). I can set the conditional formatting to highlight any
that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy

Increasing Conditional Formatting from 3 to 5
 

I have the solution!!!

I set the task on a couple of techi guys I work with and one came up trumps.
Using the following formula in conditional formatting:

Formla is - =MATCH(B2,$B$2:$G$2,0)

He then put a formula in to show how many numbers matched in each line which
is:

=SUMPRODUCT(1-ISNA(MATCH(B4:G4,B$2:H$2,0)))

Bloody brilliant, thanks for all your help with this though, maybe I've
helped others instead eh?

regards
Cathy


"CLR" wrote:

If you actually got the code where it's to go, by right clicking on the
sheet1 tab and selecting View Code, and pasteing the code in the large
window on the right......and pasteing it ALL there, including the top line
which you didn't include here......and it still don't work , then all I can
suggest is that you send your workbook to my personal addy, NOT to the
newsgroup, and I will take a look at it for you......

Vaya con Dios,
Chuck, CABGx3


"Evans9939" wrote in message
...
Hiya, I opened a new spreadsheet as you instructed and put the code in
sheet
1, this is the code I copied and pasted:

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub

I entered the numbers in A1-A5 and E1-E5 and 4 of them should have matched
up, but nothing happened - what did I do wrong?

Thanks for all your help with this, I really appreciate it
Cathy


"CLR" wrote:

Without the details, there are so many possibilities.......some of which
include, that your "numbers" are not both really numbers, but that one
set is
actually TEXT formatted, or with leading or trailing spaces,
etc..............another might be that you didn't put the code in the
proper
WorkSheet module for the sheet you are working on.........another might
be a
type-o in the modifications, etc etc etc.........
Maybe if you post your modified code back, and tell us where it's
located,
we can help more.

I tested this code in a sample workbook and it worked fine here in my
XL97.
Try opening a new workbook, pasting the code in the Sheet1 module,
putting
your list in E1:E5 of Sheet1, and typing your numbers to match in A1:A5
of
Sheet1....just as a test....A1:A5 cells should change color accordingly.

Post back if you still have difficulties....

Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Thank you

I added in your code as instructed and amended the cell details, the
spreadsheet stayed blank, I changed them round to see if it would make
a
difference - but again nothing. What am I doing wrong?

Cathy

"CLR" wrote:

Here's some modified code from an old Frank Kabel post. It assumes
your
"other set of numbers" is in E1:E5 and the ones you want to match to
them are
in A1:A5........of course those ranges and the colors can be
modified as
needed.


Private Sub Worksheet_Change(ByVal Target As Range)
' this has to go in your worksheet module:
' right-click on your tab name and choose 'code' in the context
menu
' paste your code in the appearing editor window
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1:A5")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
Select Case .Value
Case Is = Range("e1").Value: .Interior.ColorIndex = 4
Case Is = Range("e2").Value: .Interior.ColorIndex = 5
Case Is = Range("e3").Value: .Interior.ColorIndex = 6
Case Is = Range("e4").Value: .Interior.ColorIndex = 7
Case Is = Range("e5").Value: .Interior.ColorIndex = 8
End Select
End With
CleanUp:
Application.EnableEvents = True
End Sub


Vaya con Dios,
Chuck, CABGx3



"Evans9939" wrote:

Hi

I need to set conditional formatting on 5 numbers to highlight any
that
match another set of 5 numgers that will change week on week
(lottery
numbers). I can set the conditional formatting to highlight any
that match
the first 3 numbers, but not the next two.

Is there any way you can increase the conditions from 3 to 5?

Thanks Cathy