Excel/VBA EXCEL SEARCH

Advertisement


Question
QUESTION: Good day, I would like the possibility of a help of your knowledge.
I have a table with random values ​​in A1:F23, I want to compare this table with another table in H2:M20 to find equal values ​​and color these values ​​equal red.
I'm using the below VBA but is giving error "If celula.Value = H2: M20 Then".
Would you like some help, or have any suggestions of a better VBA.


Sub SearchVal ()
 'Declaring Variable
Dim celula As Object

'For Each cell between A1:F23 in sheet
For Each celula  In Range ("A1:F23")

'If Cell Value is between H2: M20 Then
If celula.Value = H2:M20 Then

  'Paint Red Cell (goes out of his Creativity)
celula.Interior.ColorIndex = 3
   end If
Next
end Sub


Att Marcus,

ANSWER: Marcus,

Try

If Application.Countif(Range("H2:M20"),celula.Value) > 0 Then

rather than  

If celula.Value = H2:M20 Then


But a better way is too use conditional formatting to test it and colour it, not VBA, and it automatically adjusts.

---------- FOLLOW-UP ----------

QUESTION: Ok Bob, thanks for listening, it was exactly what I wanted, however, he ended up highlighting everything randomly, I would compare in Table A1: E12 exact values ​​sequential H5: H6 after L5: L6 after H7: L7 after H8 H9 after L8: L9 and then H10 L10? conditional formatting is not helping me. I'm sorry for english not very clear, I am not very good at English.

Sub SearchVal ()
'Declaring Variable
Dim celula As Object

'For Each cell between A1:F23 in sheet
For Each celula  In Range ("A1:F23")

'If Cell Value is between H2: M20 Then
If Application.Countif(Range("H2:M20"),celula.Value) > 0 Then

 'Paint Red Cell (goes out of his Creativity)
celula.Interior.ColorIndex = 3
  end If
Next
end Sub




Att Marcus

Answer
Marcus,

I am not sure I understand what you are saying.

Did my suggestion work for you?

If it didn't, was it the VBA version that didn't work?

Did you try the conditional formatting approach?
Did that work?
What formula did you use?

Maybe try replying in English and also in your native language, I can use Google translate to try and work it out.

Bob
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Phillips

Expertise

Can - VBA, Functions and formulas, PowerPivot, conditional formatting, data validation, charting

Experience

I have been using Excel for in excess of 20 yeras, and I am a 9 year Microsoft MVP

Organizations
PASS UK Developer Group SQL Soton SQL FAQ

Education/Credentials
BSc Mathematics

Awards and Honors
Microsoft MVP since 2005

©2016 About.com. All rights reserved.