macro/formula to find specific values and highlight them

[raiabdal]

Hi All

Please help me with this problem. i need a macro/formula to find specific values in selected cells of excel worksheet and highlight these values. I am using excel 2000 and conditional formatting does not allow me to enter more than three conditions while i need to find upto 10 different values. thanks

 

[VoG]

Hello and welcome to MrExcel.

See if you can adapt this http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

 

[raiabdal]

Hi Peter
Thanks for your help. I am still struggling with it, may be because i have no programming background.
My excel file has other macros as well and when i try to enter this code every thing looks mixed up. i have tried to use new sheet to use this code but system goes to a brief freeze whenever i apply this code. also because the values i need to search will be changing all the time, i will have to play with the code a lot. I feel if your suggested code can be changed to a macro it can serve the purpose.
i tried to amend top lines and make a macro but was unsuccessful

 

[VoG]

Please list the range, the values that you wish to highlight and the formatting that you want applied. It shouldn't be too difficult to write a macro

 

[raiabdal]

the error in macro is coming from range selection . the line typed red is highlighted when i select debug.


Sub finduniquenumbers()

Dim Cell As Range
Dim Rng1 As Range

On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error GoTo 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)


remaining code is

Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1
Select Case Cell.Value
Case vbNullString
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
Case "Tom", "Joe", "Paul"
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
Case "Smith", "Jones"
Cell.Interior.ColorIndex = 4
Cell.Font.Bold = True
Case 11, 15, 27, 39, 42
Cell.Interior.ColorIndex = 5
Cell.Font.Bold = True
Case 1110 To 1125
Cell.Interior.ColorIndex = 6
Cell.Font.Bold = True
Case 1126 To 1199
Cell.Interior.ColorIndex = 7
Cell.Font.Bold = True
Case Else
Cell.Interior.ColorIndex = xlNone
Cell.Font.Bold = False
End Select
Next

End Sub

 

[VoG]

This should be all on one line like this

If Rng1 Is Nothing Then Set Rng1 = Range(Target.Address)

 

[raiabdal]

now it is giving error in very next line. it says Compile Error "Else without If". I have tried to put if in front, end , up and down to Else but does not work

 

[ZVI]

First af all, your code should be in Sheet module and named exactly as:

Private Sub Worksheet_Change(ByVal Target As Range)

but not as Sub finduniquenumbers(), because it should be change event macro.

See How to use article in the Peter's link and you can download the example from it

 

[VoG]

Can you post the exact code that you are using.