Lookup and highlight matched cell with colours

[Mindee]

Hi Hi,
To someone that can safe me.

I need to do up a formulated worksheet.
Lets say Cell A1(Red colour cell)
and Cell A2(Blue colour cell)
and Cell A3(Green colour cell)
and Cell A4,B4,C4,D4,E4,F4,G4(Yellow colour cell)
and Cell A5,B5,C5,D5,E5,F5,G5(Orange colour cell)
These cells above will be key in with numbers.(numbers that i need t
find)

Column A to J row 8 to 500, will have thousands over numbers keyed i
earlier.(numbers to be lookup)

Now i need help in this, If those numbers to be lookup matches wit
those key in above, that cell should change colour accordingly. And os
able to give me an auto tabulate numbers of colour strike.
Lets say "1000Red,500Blue,450Green,890Yellow,300Orange)

Thank you for you help!
I really need this help.
Thank you a million, and may god bless this someone sweet and helpful.

minde

 

[Mindee]

Please would someone help

 

[Frank Kabel]

Hi
to be honest posting the same message over and over again won't help.
As I said please stay in the original thread and comment the solutions
provided. Multiple postings will result just in the opposite: Most
people will ignore your question

 

[Bob Phillips]

Hi Mindee,

Essentially you need VBA programming. Here is an example

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Interesct(Tragte,Range("A8:J500")) Is Nothing Then
With Target
If .Count = 1 Then
If .Column = 1 Then
Select Case .Value
Case Is = 1000
.Interior.ColorIndex = 3 'red
Case Is = 30
.Interior.ColorIndex = 46 'orange
Case Is = 890
.Interior.ColorIndex = 4 'green
Case Is = 4
.Interior.ColorIndex = 6 'yellow
Case Is = 5
.Interior.ColorIndex = 8 'majenta
Case Is = 450
.Interior.ColorIndex = 5 'blue
Case Is = 7
.Interior.ColorIndex = 15 'grey
Case Is = 8
.Interior.ColorIndex = 38 'rose
Case Is = 9
.Interior.ColorIndex = 1 'teal
Case Else 'none of the above numbers
Exit Sub
End Select
End If
End If
End With

ws_exit:
Application.EnableEvents = True

End Sub

This is worksheet code and goes in the worksheet coide module (right-click
on the sheet name tab, select the View Code menu option, and paste the code
in).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Hi Mindee, you'll need code to format the cell colours as Conditional formatting
can only handle 3. An example of such code would be as follows, where the
1,2,3,4,5,6,7 etc in the Case 1, Case 2, Case3, Case 4, 5, 6 lines are the
values you have in your cells

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

For Each oCell In Range("A8:J500")
Select Case oCell.Value
'Value from A1
Case 1
oCell.Interior.ColorIndex = 3
'Value from A2
Case 2
oCell.Interior.ColorIndex = 5
'Value from A3
Case 3
oCell.Interior.ColorIndex = 50
'Values from A4:G4
Case 4, 5, 6, 7, 8, 9, 10
oCell.Interior.ColorIndex = 6
'Values from A5:G5
Case 11, 12, 13, 14, 15, 16
oCell.Interior.ColorIndex = 40
'In case any of the values are wrong
Case Else
oCell.Interior.ColorIndex = 1
End Select
Next oCell

Application.EnableEvents = True
End Sub

To use this you have to right click on the tab of your sheet and select 'view
code' and then paste the above into it.

As far as adding up how many number are in each colour, then you are better off
using formulas for that, eg:-

In cells:-
I1 => =COUNTIF($A$8:$J$500,A1)
I2 => =COUNTIF($A$8:$J$500,A2)
I3 => =COUNTIF($A$8:$J$500,A3)
I4 => =SUMPRODUCT(COUNTIF($A$8:$J$500,$A$4:$G$4))
I5 => =SUMPRODUCT(COUNTIF($A$8:$J$500,$A$5:$G$5))
I6 => =COUNT(A8:J500)-SUM(I1:I5)

The last one is an error checker, and just like the code will turn any value
that doesn't appear in your list up top completely black, this will simply count
all the number in your range and then take away the sum of the counts above.
ANY delta means something is wrong.

 

[Ken Wright]

Oops - Should have handled any possible error as Bob did, else your events
become disabled, which might not mean much to you, but just accept it's not good
for what you want. Amended ( Pinching Bobs bit verbatim :-> )


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.ScreenUpdating = False
On Error GoTo ws_exit

For Each oCell In Range("A8:J500")
Select Case oCell.Value
Case 1
oCell.Interior.ColorIndex = 3
Case 2
oCell.Interior.ColorIndex = 5
Case 3
oCell.Interior.ColorIndex = 50
Case 4, 5, 6, 7, 8, 9, 10
oCell.Interior.ColorIndex = 6
Case 11, 12, 13, 14, 15, 16
oCell.Interior.ColorIndex = 40
Case Else
oCell.Interior.ColorIndex = 1
End Select
Next oCell

Application.ScreenUpdating = True
Application.EnableEvents = True

ws_exit:
Application.EnableEvents = True

End Sub

 

[Mindee]

Thank You Guys for that kind Help. It seems abit confusing to me as i
know nothing about VBA. Will figure it out and keep you guys posted!
Once Again thank you for your kind help and not ignoring me.

thank you
mindee

 

[Ken Wright]

know nothing about VBA.

Don't let that put you off Mindee, as you don't really need to know anything
about them. Many people use them every day without the faintest idea of how it
works, usually as a result of using the Macro recorder. The following link may
help you to get going with it though:-

http://www.mvps.org/dmcritchie/excel/getstarted.htm

And just keep posting back if you have questions, as we really won't bite, and
simply want to get you sorted