macro/formula to find specific values and highlight them

raiabdal

New Member
Joined
Aug 22, 2009
Messages
4
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
 

raiabdal

New Member
Joined
Aug 22, 2009
Messages
4
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

Legend
Joined
Jun 19, 2002
Messages
63,651
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

New Member
Joined
Aug 22, 2009
Messages
4
the error in macro is coming from range selection :confused:. 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

Legend
Joined
Jun 19, 2002
Messages
63,651
This should be all on one line like this

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

raiabdal

New Member
Joined
Aug 22, 2009
Messages
4
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 :biggrin: to Else but does not work
 

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,658
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
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Can you post the exact code that you are using.
 

Forum statistics

Threads
1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top