Need Help

[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 to
find)

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

Now i need help in this, If those numbers to be lookup matches with
those key in above, that cell should change colour accordingly. And oso
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.

mindee

 

[JE McGimpsey]

One way:

Public Sub ColorLookup()
Dim rRange As Range
Dim rCell As Range
Dim rFound As Range
Dim rColor As Range
Dim sFoundAddr As String

Application.ScreenUpdating = False
Set rRange = Range("A1:A5,B4:G5")
With Range("A8:J500")
For Each rCell In rRange
Set rFound = .Find( _
What:=rCell.Value, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If Not rFound Is Nothing Then
sFoundAddr = rFound.Address
Do
If rColor Is Nothing Then
Set rColor = rFound
Else
Set rColor = Union(rColor, rFound)
End If
Set rFound = .FindNext(After:=rFound)
Loop Until rFound.Address = sFoundAddr
rColor.Interior.ColorIndex = _
rCell.Interior.ColorIndex
Set rColor = Nothing
End If
Next rCell
End With
Application.ScreenUpdating = False
End Sub




For SUM by color see

http://cpearson.com/excel/colors.htm