Increasing Conditional Formatting from 3 to 5

[Evans9939]

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

 

[Tom Hewitt]

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]

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

 

[CLR]

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]

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]

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]

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]

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]

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